Can anyone tell me where/how to include accrued interest to be paid first, when the loan repayments start, all to be settled by a fixed repayment over 12 months. I have sorted out the r(PV)/1-(1+r)n but can't work out where to put the accrued interest at the start of the repayment period, which needs to be settled first.
Replies (5)
Please login or register to join the discussion.
Try the following on excel
First row:
Cell A1 - write the rate of interest in decimals
Cell B1 - write the full loan amount
Cell C1 - write B1 x Cell $A$1$/12
Cell D1 - write the monthly payment amount
Cell E1 = B1+C1-D1
Second row:
Cell B2 = Cell E1
Cell C2 = E1 * Cell $A$1$/12
Cell D2 = Write the monthly payment amount
Cell E2 = B2+C2-D2
Third row:
Cell B3=Cell E2
And so on
Now select rows 2 & 3 and drag them down until the number of rows = number of payments, and the last column cell = 0. Copy and paste the monthly payments so these are the same cross the rows.
To get the monthly capital repayment insert a column between D & E. In the cell = D1-C1 = the monthly repayment. In other words colum D is monthly repayment and column C = IRR or the interest. D-C is the monthly repayment.
Monthly payments could be worked out by;
a) using the formula which is easy, or by
b) trial and error method on this spread sheet: keep changing the monthly payments so the last cell gets to 0 (assuming the interest rate is known)