Repayment Calculator

[messyhead]

Hi folks. I've created a spreadsheet that is used to calculate loan repayments.

It's based on an online calculator, and the figures in the spreadsheet have to match the online results. The trouble is I can't match it exactly and need help with this.

The calculator works out 2 things;

1. What the loan balance will be after 25 years (300 months) using a fixed monthly payment.

2. What the loan balance will be after 25 years (300 month) using an increasing payment based on an increasing salary.

I'll try and explain what it does at the moment;

The inputs are Salary (A), Loan amount (B), Annual Interest Rate (C), Salary Increase rate (D), Fixed Monthly Payment (E), Increasing Monthly Payment (F1-F25) i.e. one for each year.

I can work out what the fixed monthly repayment amount is based on the salary. And what the increasing monthly payment will be based on an increasing salary. The formula to work out the increasing salary is A*(1+D)^1, and then from that I work out the monthly payment. This part works ok.

The part that I can't get to match is the decreasing Loan balance e.g. what the balance would be after deducting the monthly payment, and adding the interest for that month.

The formula I'm using is;

(B-E)+((B-E)*(C/12))

This is worked out for each month, and the Loan amount for the 2nd month is the balance at the end of the previous month, and so on.

For increasing payments, the Formula would be;

(B-F1)+((B-F1)*(C/12)) for all of year 1, then F would increase for year 2, 3 and so on.

I am doing this right, is there a more accurate way? Or is there a way I can improve this method to get it to match what's getting done online.

The formulas in the spreadsheet are the same as what the code of the online calculator do.

thanks

[Mark@Work]

I note that you are deducting the payment BEFORE calculating the interest.
The repayments would be more if the interest were calculated before deducting the payment.
I suspect that the banks would therefore use the second method.
Try it and see how it compares with the online method...

Mark.

[messyhead]

I note that you are deducting the payment BEFORE calculating the interest.
The repayments would be more if the interest were calculated before deducting the payment.
I suspect that the banks would therefore use the second method.
Try it and see how it compares with the online method...

Mark.

That's got it! I never thought about it that way. I was building the formulas based on a spec of how the calc should work, but when I checked the code it's doing it the way you suggested.

Thanks very much, it's amazing what a fresh pair of eyes can do to a problem.

Tony