Mathematical conundrum on loan repayments

Mathematical conundrum on loan repayments

Didn't find your answer?

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.

avatar
By User deleted
14th Jul 2014 20:12

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.

 

 

Thanks (0)
Replying to Mikey-Mikey-Mikey:
avatar
By HeatherSimpson
15th Jul 2014 21:22

Thanks taxguru,

I have only just spotted this so will try tomorrow and see how I get on.

 

Thanks (0)
Replying to Mikey-Mikey-Mikey:
avatar
By HeatherSimpson
22nd Jul 2014 12:31

Hi taxguru


I have tried the above but just get a table similar to r(PV)1-(1=r)n but without the monthly payment being calculated for me or the capital repayment showing separately. How does it tell me how to pay the accrued interest first without compounding the interest?

Regards

Heather

Thanks (0)
avatar
By User deleted
22nd Jul 2014 12:56

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)

 

 

Thanks (0)
Replying to Justin Bryant:
avatar
By HeatherSimpson
22nd Jul 2014 14:21

: )

Thanks (0)