Question

A loan of £50,000 is repaid by annual payments of £4000 in arrear over a period of 20 years. what is the effective rate of interest per annum?

Answer 1

20 # of years of annual payments, NPER Annual installment paid, PMT Initial loan amount, PV Balance in loan account at the end, FV Annual installment mode, TYPE Effective rate of return per annum £4,000 £50,000 £O 0 = 0 if paid at the end of each period; 1 if paid at the beginning of each period 4.96% [Ref: EXCEL SCREENSHOT] EXCEL SCREENSHOT: Effective rate of return per annum is calculated using EXCEL FUNCTION RATE(nper,pmt,pv,fv, type) where nper=20; pmt=4000; pv=-50000; fv=0; type=0; Here, pv is negative as it denotes cash inflows; pmt is positive as it denotes cash outflow; type = 0 as installments paid at the end of each year only. D E F G H 20 Α. 1 # of years of annual payments, NPER 2 Annual installment paid, PMT 3 Initial loan amount, PV 4 Balance in loan account at the end, FV 5 Annual installment mode, TYPE 6 Effective rate of return per annum £4,000 £50,000 EO 0 = 0 if paid at the end of each period; 1 if paid at the beginning of each period =RATE(B1,B2,-B3,B4,B5) [Ref: EXCEL SCREENSHOTI