Loan Calculation

amillionlittlepieces

Hi Guys! Loving all the help across all the forums, keep up the good work!!

After trying to get on top of my finances (debt) I've recently been forecasting to my debt free day. One of the things I have trouble forecasting is the balance of a loan I have. It's with NatWest and each month the payment is the same but the interest paid is different meaning there's no constant to calculate future outstanding balances. The Natwest website states that "interest is calculated daily and applied quarterly". Does anyone know what this means and how to then calculate each months interest?

Was hoping to set this up in a spreadsheet to track and look forward.

Any help much appreciated!

Cornucopia

If the payment amount is always fixed, just use that, and don't worry about the interest percentage.

If you are looking to embed a notional early settlement figure in the s/sheet, I would estimate the monthly interest percentage based on the repayments and duration, and use that.

If both the payment amount and the interest amount vary, that's a weird loan you have. :eek:

DevCoder

Interest = Capital Amount * rate% * (num of days/basis)

Where num of days is the number of days you want to calculate for and basis is 365 for GBP and 360 for most other currencies.

so 10,000 @ 4% per annum calculated daily applied quarterly for the first qtr of 2013, with lets say monthly payments of 200 (paid on the 1st day of the next month to make things easier) would be

Jan - 10,000 * 4% * (31/365) = 33.97
Feb - 9,800 * 4% * (28/365) = 30.07
Mar - 9,600 * 4% * (31/365) = 32.61

96.65 Will be added as interest at the end of march value 1st april, so starting balance 1st april is 9,696.65

If my top of my head calculations are ok.

happycamel_2

Krisdorey, your calculation looks correct to me.

amillionlittlepieces

Thanks very much, will have a play around in excel and see if I can set up a sheet to track.

Much appreciated!