In: Finance
You borrow a GPM of $400,000 with annual payments and 15-year term. The interest rate is 4.5% and the payment factors from year 1 to year 15 are: 50%, 50%, 50%, 50%, 50%, 75%, 75%, 75%, 75%, 75%, 100%, …, 100%.
Sol 1 to 3: We would use PMT function in Excel to find the Annual Payment with Different Payment Factors | ||||||||||
PMT function is use to calculate the Annual Repayments; Payment Factor 50% means 50% of Interest Rate 4.5% i.e. 2.25% or 0.0225 | ||||||||||
Thus we will use =PMT(Rate,Nper,-PV,,0) where PV = 400000; Nper 15 and Rate = 0.0225 we would then get Annual Repayment as 31715.41 for first 5 years though we have calculated for 15 years initialy. | ||||||||||
PMT function is use to calculate the Annual Repayments; Payment Factor 75% means 75% of Interest Rate 4.5% i.e. 3.38% | ||||||||||
Thus we will use =PMT(Rate,Nper,-PV,,0) where PV = 281195.69 the ending balance of Year 5; Nper 10 years remaining and Rate = 3.38% we would then get Annual Repayment as 33598.67 for next 5 years though we have calculated for remaining 10 years. | ||||||||||
PMT function is use to calculate the Annual Repayments; Payment Factor 100% means 100% of Interest Rate 4.5% i.e. 4.5% | ||||||||||
Thus we will use =PMT(Rate,Nper,-PV,,0) where PV = 152238.29 the ending balance of Year 10; Nper 5 years remaining and Rate = 4.50% we would then get Annual Repayment as 34678.61 for last 5 years. | ||||||||||
PV | $4,00,000.00 | $2,81,195.69 | $1,52,238.29 | Year | Opening Balance | Principal Payment | Interest Payment | Total / Annual Repayment | Closing Balance | |
Nper | 15 | 10 | 5 | 1 | $4,00,000.00 | $22,715.41 | $9,000.00 | $31,715.41 | $3,77,284.59 | |
Rate | 0.0225 | 3.38% | 4.50% | 2 | $3,77,284.59 | $23,226.51 | $8,488.90 | $31,715.41 | $3,54,058.08 | |
PMT | ₹ 31,715.41 | ₹ 33,598.67 | ₹ 34,678.61 | 3 | $3,54,058.08 | $23,749.10 | $7,966.31 | $31,715.41 | $3,30,308.98 | |
4 | $3,30,308.98 | $24,283.46 | $7,431.95 | $31,715.41 | $3,06,025.52 | |||||
5 | $3,06,025.52 | $24,829.84 | $6,885.57 | $31,715.41 | $2,81,195.69 | |||||
6 | $2,81,195.69 | $24,108.31 | $9,490.35 | $33,598.67 | $2,57,087.37 | |||||
7 | $2,57,087.37 | $24,921.97 | $8,676.70 | $33,598.67 | $2,32,165.40 | |||||
8 | $2,32,165.40 | $25,763.09 | $7,835.58 | $33,598.67 | $2,06,402.32 | |||||
9 | $2,06,402.32 | $26,632.59 | $6,966.08 | $33,598.67 | $1,79,769.73 | |||||
10 | $1,79,769.73 | $27,531.44 | $6,067.23 | $33,598.67 | $1,52,238.29 | |||||
11 | $1,52,238.29 | $27,827.89 | $6,850.72 | $34,678.61 | $1,24,410.40 | |||||
12 | $1,24,410.40 | $29,080.14 | $5,598.47 | $34,678.61 | $95,330.26 | |||||
13 | $95,330.26 | $30,388.75 | $4,289.86 | $34,678.61 | $64,941.51 | |||||
14 | $64,941.51 | $31,756.24 | $2,922.37 | $34,678.61 | $33,185.27 | |||||
15 | $33,185.27 | $33,185.27 | $1,493.34 | $34,678.61 | $0.00 |
Ans: If Annual payments are changed to monthly then the same method will be applied as above only rate will be divided by 12 and Time Multiplied by 12 to get the Monthly Repayments.
Based on the Calculations total Annual Repayment at when repayments paid monthly are Year 1 to Year 15 = $4,88,694.57; Year 1 in specific is $31,444.07 whereas Annually when the repayments were made it was $31,715.41.
Remaining Balance at the End of Year 1 at monthly repayment was $3,77,323.02 where as in Annual Repayment it was $3,77,284.59. Interest Payment and Principal Payment when monthly repayment were made are: $8767.09 and Principal Repayment $22,676.98.
By changing the Payment from Annual Repayment to Monthly Repayment we will be repaying the whole Loan in 179 months only. Thus saving one month interest and principal.