MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

We often want to accumulate the interest or principal paid during several periods. The CUMPRINC and CUMIPMT functions make this a snap.

The CUMPRINC function computes the principal paid between two periods (inclusive). The syntax of the CUMPRINC function is CUMPRINC(rate,#per,pv,start period,end period,type). Rate, #per, pv, and type have the same meanings as described previously.

The CUMIPMT function computes the interest paid between two periods (inclusive). The syntax of the CUMIPMT function is CUMIPMT(rate,#nper,pv,start period,end period,type). Rate, #per, pv, and type have the same meanings as described previously. For example, in cell F19 on the PMT worksheet we computed the interest paid during months 2 through 4 ($161.01) by using the formula =CUMIPMT(0.08/12,10,10000,2,4,0). In cell G19 we computed the principal paid off in months 2 through 4 ($,2950.08) by using the formula =CUMPRINC(0.08/ 12,10,10000,2,4,0)

• I want to borrow $80,000 and make monthly payments for 10 years. The maximum monthly payment I can afford is $1,000. What is the maximum interest rate I can afford?

• Given a borrowed amount, the length of a loan, and the payment each period, the RATE function tells us the rate of the loan. The syntax of the RATE function is RATE(#per,pmt,pv,[fv],[type],[guess]), where fv, type, and guess are optional arguments. #per, pmt, pv, fv, and type have the same meanings as previously described. Guess is simply a guess at what the loan rate is. Usually this can be omitted. Entering in cell D9 of worksheet Rate of file Excelfinfunctions.xlsx the formula =RATE(120,–1000,80000,0,0,) yields .7241 percent as the monthly rate. We are assuming end-of-month payments. (See Figure 9-4.)

  Figure 9-4: Example of RATE function

• In cell I9 we verified the RATE function calculation. The formula =PV(.007241,120, –1000,0,0) yields $80,000.08. This shows that payments of $1,000 at the end of each month for 120 months have a present value of $80,000.08.

• Note that if you could pay back $10,000 during month 120, the maximum rate you could handle would be given by the formula =RATE(120,–1000,80000,–10000,0,0). In cell D12, this formula yields a monthly rate of 0.818 percent.

• If I borrow $100,000 at 8 percent interest and make payments of $10,000 per year, how many years will it take me to pay back the loan?

• Given the size of a loan, the payments each period, and the loan rate, the NPER function tells us how many periods it takes to pay back a loan. The syntax of the NPER function is NPER(rate,pmt,pv,[fv],[type]), where fv and type are optional arguments.

• Assuming end-of-year payments, the formula =NPER(0.08,–10000,100000,0,0) in cell D7 of worksheet Nper of file Excelfinfunctions.xlsx yields 20.91 years. (See Figure 9-5.) Thus, 20 years of payments will not quite pay back the loan, but 21 years will overpay the loan. To verify the calculation, in cells D10 and D11 we use the PV function to show that paying $10,000 per year for 20 years pays back $98,181.47, and paying back $10,000 for 21 years pays back $100,168.03.

  Figure 9-5: Example of NPER function

• Suppose that we are planning to pay back $40,000 in the final payment period. How many years will it take to pay back the loan? Entering in cell D14 the formula =NPER(0.08,–10000,100000,–40000,0) shows that it will take 15.90 years to pay back the loan. Thus, 15 years of payments will not quite pay off the loan, and 16 years of payments will slightly overpay the loan.