Excel® 2016 Formulas and Functions (2016)

---

Calculating the Interest Rate Required for a Loan

A slightly less common loan scenario arises when you know the loan term, payment, and principal, and you need to know what interest rate will satisfy these parameters. This is useful in a number of circumstances:

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:

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,” p. 93.

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 18.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.

Figure 18.9 This worksheet uses RATE() to determine the interest rate required to pay a $10,000 loan over five years at $200 per month.

Calculating How Much You Can Borrow

If you know the current interest rate your bank is offering for loans, when you want to have the loan paid off, and how much you can afford each month for the payments, you might then wonder what is the maximum amount you can borrow under those terms. To figure this out, you need to solve for the principal—that is, present value. You do this in Excel by using the PV() function:

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 18.10 shows a worksheet that calculates the maximum amount you can borrow—$25,862.78—using the following formula:

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

Figure 18.10 This worksheet uses PV() to calculate the maximum principal you can borrow, given a fixed interest rate, term, and monthly payment.

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 takes a look 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 18.11 shows an amortization schedule that takes these mortgage realities into account.

Figure 18.11 A mortgage amortization schedule that reflects the changing interest rates, amortization periods, and present value at each new term.

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:

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 18.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 18.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).

Figure 18.12 A mortgage-analysis worksheet that calculates the effect of making extra monthly paydowns toward the principal.

The Payment Adjustments area contains four values:

Payment Frequency—Use this drop-down list 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:

=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 list.)—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 18.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.

From Here

To learn how to add a list box to a worksheet, see “Using Dialog Box Controls on a Worksheet,” p. 103.

The RATE() function uses iteration to calculate its value. To learn more about iteration, see “Using Iteration and Circular References,” p. 83.

Many of the functions you learned in this chapter—including PMT(), RATE(), and NPER()—can also be used with investment calculations. See Chapter 19, “Building Investment Formulas,” p. 453.

The PV() function is most often used in discount calculations. See “Calculating the Present Value,” p. 468.