Help calculating compound interest for student loan using Excel

Flex79

Hi

Please can someone help me as I have spent days trying to figure this out without success. For various reasons which are too long to go into, I need to check the interest calculations for my student loan. I want to put this into a spreadsheet so I can do it for many years.

I have been told the interest is calculated daily, based on a daily rate e.g. 0.009438572%, and it is then applied monthly, and it is compound interest.

I have set up a spreadsheet (for every year by months) to show:
- Outstanding loan
- Amount paid off that month
- Current interest rate

I now need a column showing the month's interest but I have no idea what formula I need!

Thanks!

DevCoder

Interest = Capital * annual interest rate% * (numdays / currency basis)

where currency basis is 365 for GBP and 360 for most other currencies.

So 1,000 at 5% annual per day is

1,000 * 5% * (1/365) if GBP.

DevCoder

For daily interest , the formula you need is
=Capital * (1 + Rate)^DaysToCalculate

so 1,000 * (1+0.009438572) ^ DaysInMonth

To calculate daysinmonth then
=DAY(DATE(YEAR(A2),MONTH(A2)+1,))

so
=1000 * (1+0.009438572) ^ DAY(DATE(YEAR(A2),MONTH(A2)+1,))

Where A2 is the current date of the month you wish to calculate (obviously set this to one month behind if calculating in arrears)

Of have a2 as the last day of the month (set manually) so 30/04/2012 and then

=1000 * (1+0.009438572) ^ LEFT(A2,2)

Flex79

Hi

Thanks for this, but I think I must doing something wrong as one months interest is raising the amount from c £12K to c £16K!

Its the date bit I am struggling with as I need to do for every month from April 2003 onwards but when I do

=SUM(B4-C4)*(1+D4)^LEFT(A4,2)

Where A4 is the month -i.e April 2003
B4 is the total loan at the start (£12735.00)
C4 is that months payment paid off the loan (which is deducted before the interest is calculated - £44)
D4 is the daily interest rate (0.008351932)

I get an end result of £16,287.70!

dtsazza

You've got the daily interest rate wrong.

It's 0.00835... percent, so as a decimal/multiplier it's 0.0000835...

(You're applying nearly 1% interest per day (compounding) - no big surprise then that the end result is unpalatable!)

So either update the figure in cell D4; or if you want to keep entering it as a percentage, change the relevant part of the formula to be (1 + (D4/100)).

Flex79

Sorry if I am being thick (and thanks for the last post about the percentage as I had totally missed that!) but I am still not getting figures that make sense using the formula above.

When the interest rate goes down later in the year and the debt gets lower due to payments made the interest goes up!

Please can someone help as this is doing my head in

CLAPTON

most of the above is tosh

keep it simple to start then complicate it is you must

so

let A = current amount owing e.g. 10,000
let B = apr i.e. 1.5%
let C = monthly interest = A x B /12 e.g. 10,000 x 1.5%/12 = £1.25
let D = monthly payment e.g. 100
let E = end of month owing = A +C - D e.g 10000+1.25-100 = 901.25

so next line is

A(n) = E(n-1)

etc

this will be pretty good given I've no idea exactly how they work out months or payment dates etc

Flex79

Thanks for that but I am not sure how that gives compound interest?

I believe (although its not clear) that on the last day of the month he payment made that month is taken off the outstanding figure and the interest, which is calculated daily and applied monthly is added.

That's what I have been told but there is no explanation that details it.

I could post the spreadsheet if that would help but not sure if that's possible on here?

CLAPTON

Flex79 wrote: »

Thanks for that but I am not sure how that gives compound interest?

I believe (although its not clear) that on the last day of the month he payment made that month is taken off the outstanding figure and the interest, which is calculated daily and applied monthly is added.

That's what I have been told but there is no explanation that details it.

I could post the spreadsheet if that would help but not sure if that's possible on here?

if the algorithm is that interest accrues daily but is added monthly then that means monthly compounding.

my solution is monthly compounding

and in any event what sort of accuracy are you looking for?
daily, hourly, monthly compounding at 1.5APR is going to make very very little difference

Flex79

I don't really know what I am looking for to be honest but the accuracy doesn't have to be spot on.

I have done a new spreadsheet with the columns you suggested but don't understand A(n) = E(n-1)

I am really not good with this sort of thing, sorry

CLAPTON

well
you need a line per month


so something like this
A
12,753 1.5% 15.94 100 12,669
12,669 1.5% 15.84 100 12,584
12,584 1.5% 15.73 100 12,500

etc etc