Chapter 14

Building loan formulas

Excel is loaded with financial features that give you powerful tools for building worksheets that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment on a loan, the future value of an annuity, the internal rate of return of an investment, or the yearly depreciation of an asset. The final three chapters of this book cover these and many other uses for Excel’s financial formulas.

This chapter covers formulas and functions related to loans and mortgages. You’ll learn about the time value of money; how to calculate loan payments, loan periods, the principal and interest components of a payment, and the interest rate; and how to build an amortization schedule.

• You can invest a dollar now. If you earn a positive return, the sum of the dollar and interest earned will be worth more than the future dollar.

• You might never see the future dollar. Due to bankruptcy, cash-flow problems, or any number of other reasons, there’s a risk that the company or person promising you the future dollar might not be able to deliver it.

These two factors—interest and risk—are at the heart of most financial formulas and models. More realistically, these factors mean that you’re mostly comparing the benefits of investing a dollar now versus getting a dollar in the future plus some risk premium—an amount that compensates for the risk you’re taking in waiting for the dollar to be delivered.

You compare these by looking at the present value (the amount something is worth now) and the future value (the amount something is worth in the future). They’re related as follows:

1. Future value = Present value + Interest

2. Present value = Future value – discount

Much financial analysis boils down to comparing these formulas. If the present value in A is greater than the present value in B, A is the better investment; conversely, if the future value in B is better than the future value in A, B is the better investment.

Most of the formulas you’ll work with over the next three chapters involve these three factors—the present value, the future value, and the interest rate (or the discount rate)—plus two related factors: the periods, which are the number of payments or deposits over the term of the loan or investment, and the payment, which is the amount of money paid out or invested in each period.

When building your financial formulas, you need to ask yourself the following questions:

• Who or what is the subject of the formula? On a mortgage analysis, for example, are you performing the analysis on behalf of yourself or the bank?

• Which way is the money flowing with respect to the subject? For the present value, future value, and payment, enter money that the subject receives as a positive quantity, and enter money that the subject pays out as a negative quantity. For example, if you’re the subject of a mortgage analysis, the loan principal (the present value) is a positive number because it’s money that you receive from the bank; the payment and the remaining principal (the future value) are negative because they’re amounts that you pay to the bank.

• What is the time unit? The underlying unit of both the interest rate and the period must be the same. For example, if you’re working with the annual interest rate, you must express the period in years. Similarly, if you’re working with monthly periods, you must use a monthly interest rate.

• When are the payments made? Excel differentiates between payments made at the end of each period and those made at the beginning.

To return the periodic payment for a loan, use the PMT() function:

Click here to view code image

PMT(rate, nper, pv[, fv][, type])

For example, the following formula returns the monthly payment of a $10,000 loan with an annual interest rate of 6% (0.5% per month) over five years (60 months):

=PMT(0.005, 60, 10000)

Figure 14-1 shows an example of a worksheet set up to perform such an analysis. The PMT() formula is in cell B7, and the function arguments are stored in B4 (rate), B5 (nper), and B6 (pv).

Note two things about the formula and result in cell B7:

• The interest rate is an annual value, and the periods are expressed in years, so to get a monthly payment, you must convert these values to their monthly equivalents. This means that the interest rate is divided by 12 and the number of periods is multiplied by 12:

  =PMT(B4 / 12, B5 * 12, B6)

• The PMT() function returns a negative value, which is correct because this worksheet is set up from the point of view of the person receiving the loan, and the payment is money that flows away from that person.

You might think that the pv argument should be the partial principal—that is, the original loan principal minus the balloon amount. This seems right because the loan term is designed to pay off the partial principal. That’s not the case, however. In a balloon loan, you also pay interest on the balloon part of the principal. That is, each payment in a balloon loan has three components:

• A paydown of the partial principal

• Interest on the partial principal

• Interest on the balloon portion of the principal

Therefore, the PMT() function’s pv argument must be the entire principal, with the balloon portion as the (negative) fv argument.

For example, suppose that the loan from the previous section has a $3,000 balloon payment. Figure 14-2 shows a new worksheet that adds the balloon payment to the model and then calculates the payment using the following revised formula:

Click here to view code image

=PMT(B2 / 12, B3 * 12, B4, -B5)

Note that the balloon payment is entered into the worksheet as a positive value (because it represents, in this model, money going out), so the negation operation is used in the formula (-B5) to convert it to a negative value.

Figure 14-3 shows a worksheet that performs this calculation. In column B, cell B7 contains the total amount paid (the monthly payment multiplied by the number of months), and cell B8 takes the difference. Column C performs the same calculations on the loan with a balloon payment. As you can see, in the balloon payment scenario, the payment total is about $2,600 smaller, but the total interest is about $400 higher.

To calculate the principal and interest components of a loan payment, use the PPMT() and IPMT() functions, respectively:

Click here to view code image

PPMT(rate, per, nper, pv[, fv][, type])

IPMT(rate, per, nper, pv[, fv][, type])

Figure 14-4 shows a worksheet that applies these functions to the loan. The table shows the principal (column F) and interest (column G) components of the loan for the first 10 periods and for the final period. Note that with each period, the principal portion increases and the interest portion decreases. However, the total remains the same (as confirmed by the Total column), which is as it should be because the payment remains constant through the life of the loan.

Click here to view code image

=IPMT(rate, ROW(INDIRECT("A1:A" & nper)), nper, pv[, fv][, type])

The array of per values is generated by the following expression:

ROW(INDIRECT("A1:A" & nper))

The INDIRECT() function converts a string range reference into an actual range reference, and then the ROW() function returns the row numbers from that range. By starting the range at A1, this expression generates integer values from 1 to nper, which covers the life of the loan.

For example, here’s an array formula that calculates the total interest cost of the loan model shown in Figure 14-4:

Click here to view code image

=SUM(IPMT(B2 / 12, ROW(INDIRECT("A1:A" & B3 * 12)), B3 * 12, B4))

You could solve these kinds of problems by building a model that uses the PPMT() and IPMT() functions over the time frame you’re dealing with and then summing the results. However, Excel has two functions that offer a more direct route:

Click here to view code image

CUMPRINC(rate, nper, pv, start_period, end_period, type)

CUMIPMT(rate, nper, pv, start_period, end_period, type)

The main difference between CUMPRINC() and CUMIPMT() and PPMT() and IPMT() is the start_period and end_period arguments. For example, to find the cumulative principal or interest in the first year of a loan, you set start_period to 1 and end_period to 12; for the second year, you set start_period to 13 and end_period to 24. Here are a couple of formulas that calculate these values for any year, assuming that the year value (1, 2, and so on) is in cell D2:

Click here to view code image

start_period: (D2 - 1) * 12 + 1
end_period: D2 * 12

Figure 14-5 shows a worksheet that returns the cumulative principal and interest paid in each year of a loan, as well as the total principal and interest for all five years.

• The values for the four main arguments of the payment functions are stored in the range B2:B5.

• The amortization schedule is shown in A9:G18. Column A contains the period, and subsequent columns calculate the payment (B), principal component (C), interest component (D), cumulative principal (E), and cumulative interest (F). The Remaining Principal column (G) shows the original principal amount (B4) minus the cumulative principal for each period.

• The cumulative principal and interest values are calculated by adding the running totals of the principal and interest components. You need to do this because the CUMPRINC() and CUMIPMT() functions don’t work with balloon payments. If you never use balloon payments, you can convert the worksheet to use these functions.

• This schedule uses a yearly time frame, so no adjustments are applied to the rate and nper arguments.

The amortization schedule in Figure 14-6 assumes that the interest rate remains fixed throughout the life of the loan. To learn how to build an amortization schedule for a variable-rate loan, see “Building a variable-rate mortgage amortization schedule,” later in this chapter.

• If you want to use a different time basis—for example, monthly instead of annual—you need to edit the initial formulas for payment, principal, interest, cumulative principal, and cumulative interest, and then refill the schedule.

• If you want to use a different number of periods, you need to either extend the schedule (for a longer term) or shorten the schedule and delete the extraneous periods (for a shorter term).

Both operations are tedious and time-consuming enough that they greatly reduce the value of the amortization schedule. To make the schedule truly useful, you need to reconfigure it so that the schedule formulas and the schedule itself adjust automatically to any change in the time basis or the length of the term.

Figure 14-7 shows a worksheet that implements such a dynamic amortization schedule.

Here’s a summary of the changes you make to create this schedule’s dynamic behavior:

• To change the time basis, select a value—Annual, Semi-Annual, Quarterly, or Monthly—in the Time Basis drop-down menu. These values come from the text literals in the range G3:G6. The number of the selected list item is stored in cell F2.

• The time basis determines the time factor, the amount by which you have to adjust the rate and the term. For example, if the time basis is Monthly, the time factor is 12. This means that you divide the annual interest rate (C2) by 12, and you multiply the term (C3) by 12. These new values are stored in the Adjusted Rate (E4) and Total Periods (E5) cells. The Time Factor cell (E3) uses the following formula:

  =CHOOSE(F2, 1, 2, 4, 12)

• Given the adjusted rate (E4) and the total periods (E5), the schedule formulas can reference these cells directly and always return the correct value for any selected time basis. For example, here’s the formula that calculates the payment:

  =PMT(E4, E5, C4, C5, C6)

• The schedule adjusts its size automatically, depending on the Total Periods value (E5). If Total Periods is 15, the schedule contains 15 rows (not including the headers); if Total Periods is 180, the schedule contains 180 rows.

• Dynamically adjusting the size of the schedule is a function of the Total Periods value (E5). The first period (A10) is always 1; each subsequent period checks the previous value to see if it’s less than Total Periods. Here’s the formula in cell A11:

  =IF(A10 < $E$5, A10 + 1, "")

  If the period value of the cell above the current cell is less than Total Periods, the current cell is still within the schedule, so calculate the current period (the value from the cell above plus 1) and display the result; otherwise, you’ve gone past the end of the schedule, so display a blank.

• The various payment columns check the period value. If it’s not blank, calculate and display the result; otherwise, display a blank. Here’s the formula for the Payment value in B11:

  Click here to view code image

  =IF(A11 <> "", PMT($E$4, $E$5, $C$4, $C$5, $C$6), "")

These changes result in a totally dynamic schedule that adjusts automatically as you change the time basis or the term.

You could figure out the term by adjusting the nper argument of the PMT() function until you get the payment you want. However, Excel offers a more direct solution in the form of the NPER() function, which returns the number of periods of a loan:

Click here to view code image

NPER(rate, pmt, pv[, fv][, type])

For example, suppose that you want to borrow $10,000 at 6% interest with no balloon payment, and the most you can spend is $750 per month. What term should you get? Figure 14-8 shows a worksheet that uses NPER() to calculate the answer: 13.8 months. Here are some things to note about this model:

• The interest rate is an annual value, so the NPER() function’s rate argument divides the rate by 12.

• The payment is already a monthly number, so no adjustment is necessary for the pmt attribute.

• The payment is negative because it’s money that you pay to the lender.

Of course, in the real world, although it’s not unusual to have a noninteger term, the last payment must occur at the beginning or end of the last loan period. In the example, the bank uses the term 13.8 months to calculate the payment, principal, and interest, but it rightly insists that the last payment be made at either the 13th period or the 14th period. The tables after the NPER() formula in Figure 14-8 investigate both scenarios.

If you elect to end the loan after the 13th period, you’ll still have a bit of principal left over. To see why, the amortization table shows the period (column A) as well as the principal paid each period (column B), as returned by the PPMT() function. The Cumulative Principal column (column C) shows a running total of the principal. As you can see, after 13 months, the total principal paid is only $9,378.07, which leaves $621.93 remaining (cell C24). Therefore, the 13th payment will be $1,371.93 (the usual $750 payment, plus the remaining $621.93 principal).

If you elect to end the loan after the 14th period instead, you’ll end up overpaying the principal. To see why, the second amortization table shows the Period (column E), Principal (column F), and Cumulative Principal (column G) columns. After 14 months, the total principal paid is $10,124.96, which is $124.96 more than the original $10,000 principal. Therefore, the 14th payment will be $625.04 (the usual $750 payment minus the $124.96 principal overpayment).

• You might want to wait until interest rates fall to the value you want.

• You might regard the calculated interest rate as a maximum rate that you can pay, knowing that anything less will enable you to reduce either the payment or the term.

• You could use the calculated interest rate as a negotiating tool with your lender by asking for that rate and walking away from the deal if you don’t get it.

To determine the interest rate given the other loan factors, use the RATE() function:

Click here to view code image

RATE(nper, pmt, pv[, fv][, type][, guess])

The RATE() function’s guess argument indicates that this function uses iteration to determine the answer. To learn more about iteration, see “Using iteration and circular references,” in Chapter 2, “Creating advanced formulas.”

For example, suppose you want to borrow $10,000 over five years with no balloon payment and a monthly payout of $200. What rate will satisfy these criteria? The worksheet in Figure 14-9 uses RATE() to derive the result: 7.4%. Here are some notes about this model:

• The term is in years, so the RATE() function’s nper argument multiplies the term by 12.

• The payment is already a monthly number, so no adjustment is necessary for the pmt attribute.

• The payment is negative because it’s money that you pay to the lender.

• The result of the RATE() function is multiplied by 12 to get the annual interest rate.

Click here to view code image

PV(rate, nper, pmt[, fv][, type])

For example, suppose the current loan rate is 6%, you want the loan paid off in five years, and you can afford payments of $500 per month. Figure 14-10 shows a worksheet that calculates the maximum amount you can borrow—$25,862.78—using the following formula:

Click here to view code image

=PV(B2 / 12, B3 * 12, B4, B5, B6)

Case study: Working with mortgages

For both businesses and people, a mortgage is almost always the largest financial transaction. Whether it’s millions of dollars for a new building or hundreds of thousands of dollars for a house, a mortgage is serious business. It pays to know exactly what you’re getting into, both in terms of long-term cash flow and in terms of making good decisions up front about the type of mortgage so that you minimize your interest costs. This case study looks at mortgages from both points of view.

Building a variable-rate mortgage amortization schedule

For simplicity’s sake, it’s possible to build a mortgage amortization schedule like the ones shown earlier in this chapter. However, these are not always realistic because a mortgage rarely uses the same interest rate over the full amortization period. Instead, you usually have a fixed rate over a specific term (usually one to five years), and you then renegotiate the mortgage for a new term. This renegotiation involves changing three things:

• The interest rate over the coming term, which will reflect current market rates.

• The amortization period, which will now be shorter by the length of the previous term. For example, a 25-year amortization will drop to a 20-year amortization after a 5-year term.

• The present value of the mortgage, which will be the remaining principal at the end of the term.

Figure 14-11 shows an amortization schedule that takes these mortgage realities into account.

Here’s a summary of what’s happening with each column in the amortization:

• Amortization Year: This column gives the year of the overall amortization. This is mainly used to help calculate the Term Period values. Note that the values in this column are generated automatically based on the value in the Amortization (Years) cell (B3).

• Term Period: This column gives the year of the current term. This is a calculated value (it uses the MOD() function) based on the value in the Amortization Year column and the value in the Term (Years) cell (B4).

• Interest Rate: This is the interest rate applied to each term. You enter these rates by hand.

• NPER: This is the amortization period applied to each term. It’s used as the nper argument for the PMT(), PPMT(), and IPMT() functions. You enter these values by hand.

• Payment: This is the monthly payment for the current term. The PMT() function uses the Interest Rate column value for the rate argument and the NPER column value for the nper argument. For the pv argument, the function grabs the remaining balance at the end of the previous term by using the OFFSET() function in the following general form:

  Click here to view code image

  OFFSET(current_cell, -Term_Period, 5)

  In this formula, current_cell is a reference to the cell containing the formula, and Term_Period is a reference to the corresponding cell in the Term Period column. For example, here’s the formula in E11:

  OFFSET(E11, -B11, 5)

  Because the value in B11 is 1, the function goes up one row and right five columns, which returns the value in J10 (in this case, the original principal).

• Principal and Interest: These columns calculate the principal and interest components of the payment, and they use the same techniques as the Payment column.

• Cumulative Principal and Cumulative Interest: These columns calculate the total principal and interest paid through the end of each year. Because the interest rate isn’t constant over the life of the loan, you can’t use CUMPRINC() and CUMIPMT(). Instead, these columns use running SUM() functions.

• Remaining Principal: This column calculates the principal left on the loan by subtracting the value in the Principal column for each year. At the end of each term, the Remaining Principal value is used as the pv argument in the PMT(), PPMT(), and IPMT() functions over the next term. In Figure 14-11, for example, at the end of the first five-year term, the remaining principal is $89,725.43, so that’s the present value used throughout the second five-year term.

Allowing for mortgage principal paydowns

Many mortgages today allow you to include in each payment an extra amount that goes directly to paying down the mortgage principal. Before you decide to take on the financial burden of these extra paydowns, you probably want two questions answered:

• How much more quickly will I pay off the mortgage?

• How much money will I save over the amortization period?

Both questions are easily answered using Excel’s financial functions. Consider the mortgage-analysis model I’ve set up in Figure 14-12. The Initial Mortgage Data area shows the basic numbers needed for the calculations: the annual interest rate (cell B2), the amortization period (B3), the principal (B4), and the paydown that is to be added to each payment (B5; notice that this is a negative number because it represents a monetary outflow).

The Payment Adjustments area contains four values:

• Payment Frequency: Use this drop-down menu to specify how often you make your mortgage payments. The available values—Annual, Monthly, Semi-Monthly, Bi-Weekly, and Weekly—come from the range G2:G6; the number of the selected list item is stored in cell F2.

• Payments Per Year (E3): This is the number of payments per year, as given by the following formula:

  Click here to view code image

  =CHOOSE(F2, 1, 12, 24, 26, 52)

• Rate Per Payment: This is the annual rate divided by the number of payments per year.

• Total Payments: This is the amortization value multiplied by the number of payments per year.

The Mortgage Analysis area shows the results of various calculations:

• Frequency Payment (Frequency is the selected item in the drop-down menu.): The Regular Mortgage payment (E9) is calculated using the PMT() function, where the rate argument is the Rate Per Payment value (E4) and the nper argument is the Total Payments value (E5):

  =PMT(E4, E5, B4, 0, 0)

  The With Extra Payment value (F9) is the sum of the Paydown (B5) and the Regular Mortgage payment (E9).

• Total Payments: For the Regular Mortgage (E10), this is the same as the Total Payments value (E5). It’s copied here to make it easy for you to compare this value with the With Extra Payment value (F10), which calculates the revised term with the extra paydown included. It does this with the NPER() function, where the rate argument is the Rate Per Payment value (E4) and the pmt argument is the payment in the With Extra Payment column (F9).

• Total Paid: These values multiply the Payment value by the Total Payments value for each column.

• Savings: This value (cell F12) takes the difference between the Total Paid values to show how much money you save by including the paydown in each payment.

In the example shown in Figure 14-12, paying an extra $100 per month toward the mortgage principal reduces the term on a $100,000 mortgage from 300 months (25 years) to 223.4 months (about 18.5 years) and reduces the total amount paid from $193,290 to $166,251, a savings of $27,039.