Building Loan Formulas - Building Financial Formulas - Excel® 2016 Formulas and Functions (2016)

Excel® 2016 Formulas and Functions (2016)

Part IV: Building Financial Formulas

18. Building Loan Formulas

In This Chapter

Understanding the Time Value of Money

Calculating a Loan Payment

Building a Loan Amortization Schedule

Calculating the Term of a Loan

Calculating the Interest Rate Required for a Loan

Calculating How Much You Can Borrow

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.

Understanding the Time Value of Money

The time value of money means that a dollar in hand now is worth more than a dollar promised at some future date. This seemingly simple idea underlies not only the concepts and techniques you learn in this chapter but also the investment formulas in Chapter 19, “Building Investment Formulas,” and the discount formulas in Chapter 20, “Building Discount Formulas.” A dollar now is worth more than a dollar promised in the future for two reasons:

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

Image 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 really 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:

A. Future value = Present value + Interest

B. 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:

Image 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?

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

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

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

Calculating a Loan Payment

When negotiating a loan to purchase equipment or a mortgage for your house, the first concern that comes up is almost always the size of the payment you’ll need to make each period. This is just basic cash-flow management because the monthly (or whatever) payment must fit within your budget.

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

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)

Loan Payment Analysis

Financial formulas rarely use hard-coded function arguments. Instead, you almost always are better off placing the argument values in separate cells and then referring to those cells in the formula. This enables you to do a rudimentary form of loan analysis by plugging in different argument values and seeing the effects they have on the formula result.

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


Figure 18.1 To perform a simple loan analysis, place the PMT() function arguments in separate cells and then change those cell values to see the effect on the formula.


You can download this chapter’s sample workbook at

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

Image 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)

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

Working with a Balloon Loan

Many loans are set up so that the payments take care of only a portion of the principal, with the remainder due as an end-of-loan balloon payment. This balloon payment is the future value of the loan, so you need to factor it into the PMT() function as the fv argument.

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:

Image A paydown of the partial principal

Image Interest on the partial principal

Image 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 18.2 shows a new worksheet that adds the balloon payment to the model and then calculates the payment using the following revised formula:

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


Figure 18.2 To allow for an end-of-loan balloon payment, add the fv argument to the PMT() function.

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.

Calculating Interest Costs, Part 1

When you know the payment, you can calculate the total interest costs of a loan by first figuring the total of all the payments and then subtracting the principal. The remainder is the total interest paid over the life of the loan.

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


Figure 18.3 To calculate total interest paid out over the life of a loan, multiply the periodic payment by the number of periods and then subtract the principal paid.

Calculating the Principal and Interest

Any loan payment has two components: principal repayment and interest charges. Interest charges are almost always front loaded, which means that the interest component is highest at the beginning of the loan and gradually decreases with each payment. This means, conversely, that the principal component increases gradually with each payment.

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

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


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


Figure 18.4 This worksheet uses the PPMT() and IPMT() functions to break out the principal and interest components of a loan payment.

Calculating Interest Costs, Part 2

Another way to calculate the total interest paid on a loan is to sum the various IPMT() values over the life of the loan. You can do this by using an array formula that generates the values of the IPMT() function’s per argument. Here’s the general formula:

{=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 a formula that calculates the total interest cost of the loan model shown in Figure 18.4:

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


The array formula doesn’t work if the loan includes a balloon payment.

Calculating Cumulative Principal and Interest

Knowing how much principal and interest you pay each period is useful, but it’s usually handier to know how much principal or interest you’ve paid in total up to a given period. For example, if you sign up for a mortgage with a five-year term, how much principal will you have paid off by the end of the term? Similarly, a business might need to know the total interest payments a loan requires in the first year so that it can factor the result into its expense budgeting.

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:

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



In both CUMPRINC() and CUMIPMT(), all of the arguments are required. If you omit the type argument (which is optional in most other financial functions), Excel returns the #N/A error.

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:

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

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


Figure 18.5 This worksheet uses the CUMPRINC() and CUMIPMT() functions to return the cumulative principal and interest for each year of a loan.


Note that the CUMIPMT() function gives you an easier way to calculate the total interest costs for a loan. Just set start_period to 1 and end_period to the number of periods (the value of nper).


Although the CUMPRINC() function works as advertised if the loan includes a balloon payment, the CUMIPMT() function does not.

Building a Loan Amortization Schedule

A loan amortization schedule is a table that shows a sequence of calculations over the life of a loan. For each period, the schedule shows figures such as the payment, the principal and interest components of the payment, the cumulative principal and interest, and the remaining principal. The next few sections take you through various amortization schedules designed for different scenarios.

Building a Fixed-Rate Amortization Schedule

The simplest amortization schedule is just a straightforward application of three of the payment functions you’ve seen so far: PMT(), PPMT(), and IPMT(). Figure 18.6 shows the result, which has the following features:

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

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

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

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


Figure 18.6 This worksheet shows a basic amortization schedule for a fixed-rate loan.

Image The amortization schedule in Figure 18.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, seeBuilding a Variable-Rate Mortgage Amortization Schedule,” p. 447.

Building a Dynamic Amortization Schedule

The problem with the amortization schedule in Figure 18.6 is that it’s static. It works well if you change the interest rate or the principal, but it doesn’t handle other types of changes very well:

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

Image 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 18.7 shows a worksheet that implements such a dynamic amortization schedule.


Figure 18.7 This worksheet uses a dynamic amortization schedule that adjusts automatically to changing the time basis or the length of the term.

Here’s a summary of the changes you make to create this schedule’s dynamic 'margin-top:4.0pt;margin-right:0cm;margin-bottom:4.0pt; margin-left:40.0pt;text-indent:-9.5pt;line-height:normal'>Image To change the time basis, select a value—Annual, Semiannual, Quarterly, or Monthly—in the Time Basis drop-down list. These values come from the text literals in the range G3:G6. The number of the selected list item is stored in cell F2.

Image To learn how to add a list box to a worksheet, seeUsing Dialog Box Controls on a Worksheet,” p. 103.

Image 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)

Image 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 expression that calculates the payment:

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

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

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

Image 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:

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


The formulas in the amortization schedule have been filled down to row 500, which should be enough room for just about any schedule (up to about 40 years, using the monthly basis). If you require a longer schedule, you’ll have to fill in the schedule formulas past the last row that will appear in your schedule.

Calculating the Term of a Loan

In some loan scenarios, you need to borrow a certain amount at the current interest rates, but you can spend only so much on each payment. If the other loan factors are fixed, the only way to adjust the payment is to adjust the term of the loan: A longer term means smaller payments; a shorter term means larger payments.

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:

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 18.8 shows a worksheet that uses NPER() to calculate the answer: 13.8 months. Here are some things to note about this model:

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

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

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


Figure 18.8 This worksheet uses NPER() to determine the number of months that a $10,000 loan should be taken out at 6% interest to ensure a monthly payment of $750.

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


The cumulative principal values are calculated using the SUM() function. You can’t use the CUMPRINC() function in this case because CUMPRINC() truncates the nper argument to an integer value.

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


Another way to calculate the principal that is left over or overpaid is to use the FV() function, which returns the future value of a series of payments. For the 13-month scenario, you run FV() with the nper argument set to 13 (see cell C25 in Figure 18.8); for the 14-month scenario, you run FV() with the nper argument set to 14 (see cell G26). You’ll learn about FV() in detail in Chapter 19.

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:

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

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

Image 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])


Image The RATE() function’s guess argument indicates that this function uses iteration to determine the answer. To learn more about iteration, seeUsing 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:

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

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

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

Image 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:

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

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

Image 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:

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

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

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

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

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

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

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

Image 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:

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

Image 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:

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

Image 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)

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

Image 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:

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

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

Image Total Paid—These values multiply the Payment value by the Total Payments value for each column.

Image 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

Image To learn how to add a list box to a worksheet, seeUsing Dialog Box Controls on a Worksheet,” p. 103.

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

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

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