Question

You borrow money on a self liquidating installemnt loan (equal payments at the end of each year, each payment is part principal part interest) Loan Interest Rate Life (years) Date of Loan $902,000 12.80% 49 January 1, 2019 Use the installment method-not straight line Do NOT round any interrmediate numbers. Do NOT turn this into a monthly problem. a) What is the annual payment? b) What are the total interest payments? c) After 29 payments have been made, what percentage of thetotal interest has been paid (round to the nearest percentage point)? d) After 29 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)?

Answer 1

Annual Interest rate, RATE # of monthly payments of debt, NPER Loan amount, PV Balance in loan at the end, FV Equal annual installment type, TYPE 12.80% 49 $902,000 $0 [As all will be paid off over loan's tenure] 0 0 if installments paid at the end of each year; 1 f installments paid at the beginning of each year 115,772.56 Ref: NOTE a) Equal annual payment required b) Total interest paid over the loan period 4,770,855.57 [Ref: NOTE] c) Percentage of interest paid after 29 years $4,770,855.57 68.72% [Interest paid over 29 years/ Total interest; Ref: NOTE 8.74% Principal paid over 29 years / Loan amount; Ref: NOTE] d) Percentage of principal paid after 29 years NOTE: Equal annual payment required is calculated using EXCEL FUNCTION PMT(rate,nper,pv,fv,type) where rate-12.80%; nper-49; pv-902000; fv:0; type:0; Here, pv is negative as it denotes cash inflow; type- as annual installments are paid at the end of each year only Total interest paid over the loan period is calculated using EXCEL FUNCTION CUM!PMT(rate, nper,pv, start period, end period,type) where rate-12.80%; nper-49; pV-902000; start period=1; end period:49; type:0. Here, type =0 as installments paid at the end of each year only; start period = 1 as we are calculating the interest paid from installment-1. End-period = 49 as we are calculating the interest paid till installment-49

Interest paid over 29 years is calculated using EXCEL FUNCTION CUMIPMT(rate,nper,pv, start period, end period,type) where rate-12.80%; nper-49; pv=902000; start period-1; end period-29; type-0. Here, type 0 as installments paid at the end of each year only; start_period 1 as we are calculating interest paid from installment-1; End_period 29 as we are calculating the interest paid till installment-29 Principal paid over 29 years is calculated using EXCEL FUNCTION CUMPRINC(rate,nper, pv, start period, end period,type) where rate-12.80%; nper-49; pv=902000; start period-1; end period-29; type-0. Here, type 0 as installments paid at the end of each year only; start_period 1 as we are calculating principal paid from installment-1; End_period 29 as we are calculating the principal paid till installment-29 EXCEL SCREENSHOT 1 Annual Interest rate, RATE 2 # of monthly payments of debt, NPER 3 Loan amount, PV 4 Balance in loan at the end, FV 5 Equal annual installment type, TYPE 12.80% 49 $902,000 $0 [As all will be paid off over loan's tenure] 0-0 if installments paid at the end of each year; 1 if installments paid at the beginning of each year 7 a) Equal annual payment required PMT(B1,B2,-B3,B4,B5) [Ref: NOTE] 9 b) Total interest paid over the loan periodCUMIPMT(81,B2,83,1,82,0) [Ref: NOTE] 10 11 c Percentage of nterest paid after 29 years -CUMIP T B1 B2 B3 1,29, 5199 Interest paid over 29 years / Total interest; Ref: NOTE 12 13 d) Percentage of principal paid after 29 yearsCUMPRINC(B1,B2,B3,1,29,B5)/B3 [Principal paid over 29 years/Loan amount; Ref: NOTE