I’m building a simple loan calculation for a customer. Nothing exciting and there’s lots of online tools that do it, yet I’m stumped.
One is based on calculating total loan repayment and monthly loan repayment. The other is based on selecting the monthly budget and working out how much the client can borrow over the term.
My calculations are out by about £100-200, but why?

I’m using the ‘PMT’ formula in excel, but the answer varied from the online tool and I’m not sure why..

Essentially I’m looking for the formulas that drive the 2 parts (‘Loan Amount’ & ‘Monthly Budget’) of this calculator- MoneySupermarket

For the sake of an example;
Loan amount-
> Loan: £100,000
> Term: 36 months
> Interest: 2.7%
= Monthly payments of £??
= Total repayment of £??

Monthly budget:
> Monthly payment: £3,000
> Term: 36 months
> Interest: 2.7%
= Loan available: £??
= Total repayable: £??

Any chance someone could help me?

Rounding?

If I was doing it I would look at calc from first principles

Are you compounding the interest properly?

I make the answer to the top part a monthly repayment of £3009 with a total repayable of £108321. This is all rounded to the nearest pound.

Example 1.

Using PMT function (payable at month end) I get £2895 pm (same as MSM)
Using cashflow amortisation I get the same figure.
total repayable £104,238

Example 2
£103,630 loan available
Total repayable £108,000 (36 x £3k)

cokie, go here and I can take you through it if you want
https://docs.google.com/spreadsheets/d/1DkJHO5kLASWl7RJtvZcpPfsP4cbQH5ZUDCarwdZZxKc/edit?usp=sharing

How does the formula look for that Gonefishin please?
I think that may be the issue.. possibly rounded too, given the low figures.

EDIT: Awesome! Thank you.
Just having a look now. I know its small, but there’s still a discrepancy in your figures. Where does that come from, given that you’ve included decimal places? I’m trying to avoid the client testing the online tool and finding the discrepancy.

Total repayable = 100000*(1+2.7%)^(36/12)

Then divide total repayable by 36 to get monthly.

That’s the sort of first principles approach that I would take and it makes sense mathematically. N.B. I have assumed that the interest rate is an annualised number.

gf – you cant do that as you are amortising through the loan from the single payment.

Were you not amortising and just compounding interest then yes the total would be the balance at the end, but 1/36 would not be the single periodic payment

My quick method, divide by 2 and multiply by the interest rate to give the average yearly interest. Multiply that by the number of years and that is your overall interest. Add it to initial and then divide by number of months.

£100,000 * 0.5 = £50,000
£50,000 * 0.027 = £1350 average interest per year
£1350 *3 = £4050 total interest

Total amount paid = £104,050
Monthly payment = £104,450 / 36 = £2890.28

Loan calculator on windows 7 calculator (See worksheets) gives £2894.92

Thanks for all the help.
Big thanks to Stoner too for explaining it to me.

Still none the wiser on the second ‘Monthly budget’ formula..

Still none the wiser on the second ‘Monthly budget’ formula..

updated in the worksheet for you. but for others:

you use the FV formula but with a Negative interest rate (as it’s a loan not investment bond)

Yup, thanks Stoner! Very grateful- saved me a lot of trouble.

I think the slight difference may be because APR is an annual rate and the equivalent monthly rate is 2.667%. Also if you know the Payment (P)for £100,000, then to calculate how much a £3,000 payment would generate is simply 3,000/P X 100,000.

good point mefty.

12 x ((1+2.7%)^(1/12) -1 ) = 2.667%

Are you calculating and charging interest on a daily or monthly basis?

unless an individual lender states otherwise, one has to assume that the charging period is the same as the payment period in the Moneysupermarket calculator. Which is the basis we are trying to recreate.

But daily vs monthly is one of the things that will throw up anomalies in a model unless you know the terms of the loan.

EDIT: PS Cokie, I was being stoopid re the FV formula. Sheet Updated to use the correct PV formula instead.

So you are doing it for a customer , which means that it’s your job and yet you have to come on a bike forum to find out how to work the interest out . Didn’t you have to take exams and get qualifications to prove that you could do sort of thing before they let you loose with customers money ?

TBF to the OP, he knew he was coming up with anomalies* and is looking to learn why. He’s used STW, but could easily have gone to Ozgrid/MrExcel/finance forum/PistonHeads/Mumsnet and probably got assistance. You dont arrive at every problem already knowing the solution.

I model all sorts of weird stuff that normally might be outside of my experience from Hotel Restaurants to Biomass Power Generation stations or CHP Plants in Kazakhstan. It’s fun to stretch your comfort zone.

*Working out why you are wrong is really important

one has to assume

I don’t do assume. It’s bad.

It’s worse than that. It makes an ass of team. Or something.

What @mefty says would be my first guess too wrt the discrepancy

Ramsey Neil earlier today

My mortgage advisor spreadsheet:

https://docs.google.com/spreadsheets/d/1cLH5NU8s4ghbiB7wlc_a1obAcjXdY59TZuW5jhStqjc/edit?usp=sharing

@sharkbait yes I understand that it must look like I’m just being a smart arse but I really didn’t mean to appear as such . I am genuinely surprised that anybody who does mortgages , loans etc can’t just bring up the repayments on a computer .