Question

Please show how to get your answer using excel.

Exercise 2.7: Sandy borrows $15,000 at a compound interest of 6% per year from a bank over a seven-year period. What will be the amount of seven equal yearly payments that Sandy will have to make to the bank if she made the payment: a. At the starting of each year? b. At the end of each year?

Answer 1

Annual Interest rate, RATE # of annual payments of debt, NPER Initial loan deposit, PV Balance in loan account at the end, FV Annual installment type, TYPE Equal annual payment required Part-(a) Part-(b) (Start of year) (End of year) 6% 6% 7 7 $15,000 $15,000 $0 $0 (Because all will be paid off over loan's tenure] 1 0 = 0 if installments paid at the end of each year; 1 if installments paid at the beginning of each year $2,534.93 $2,687.03 [Ref: NOTE] NOTE: For Part-(a): Equal annual payments required is calculated using EXCEL FUNCTION PMT(rate,nper,pv,fv,type) where rate=6%; nper=7; pv=-15000; fv=0; type=1; Here, pv is negative as it denotes cash inflow; type=1 as installments are paid at the beginning of each year itself, starting from today.

For Part-(b): Equal annual payments required is calculated using EXCEL FUNCTION PMT(rate,nper,pv,fv,type) where rate=6%; nper=7; pv=-15000; fv=0; type=0; Here, pv is negative as it denotes cash inflow; type=0 as installments are paid at the end of each year only. В. D E F G H I J K L Part-(a) Part-(b) (Start of year) (End of year) 3 Annual Interest rate, RATE 6% 4 # of annual payments of debt, NPER 7 5 Initial loan deposit, PV $15,000 $15,000 6 Balance in loan account at the end, FV $0 $0 [Because all will be paid off over loan's tenure] 7 Annual installment type, TYPE 0 = 0 if installments paid at the end of each year; 1 if installments paid at the beginning of each year 8 Equal annual payment required =PMT(B3,B4,-B5, B6,B7) EPMT(C3,C4,-C5,C6,C7) [Ref: NOTE] 6%