Help Excel help need to workout Loan Repayments

ahll

Hi all,
I am currently trying to get an Excel spreadsheet together so that I can calculate how much buying a car is going to cost me. I want to be able to take the spreadsheet with me to verify any figures given to me so I can make a really informed choice.

I have done most of it but I am struggling to workout the Interest that will be due which I am lead to believe is compound interest. I have be using the FV function but some how what I am coming up with doesn't match what is show on a website I have been told about. This is the site HERE

On the site you can input years or months so I would like to be able to do that on my spreadsheet.

On this site when I put in £4012.32 over 35 months at an Apr of 6.9% it gives me Total Charge of £441.08

However when I do it on my spreadsheet it comes out with £891.64 !

The formula I am using is =FV(0.00575,35,,4012.32)
Monthly rate (6.90%/12) = 0.00575
Monthly term = 35
Capital Amount = £4012.32

Does anyone know how to get my spreadsheet working properly ?

AlwaysSomething

I don't think you want to use FV. What you are calculating is a bit like this:
"I invest 4012.32, and receive interest every month. How much will I have at the end?" In that example, the 4012 remains invested throughout - that's why you get a lot of interest. When you are repaying a loan, it's different. The amount owing drops each month. At the beginning, you owe 4012.32. After 1 month, you have repaid some of the loan, so the amount on which interest is charged goes down. You end up with a lot less interest.
I did =PMT(0.069/12,35,4012.32,,0) to calculate the monthly payment required to pay off the loan in 35 months. That gave 126.89 (the monthly payment). Multiply that by 35 and you get 4441.08 which is very close to your 4453.

bod1467

Bear in mind also that the load will include admin fees/commission and it is these that are paid off first before any of the loan is repaid. Thus early on (first couple of months or so) the amount owed (hence interest accrued) is on the full amount of the "loan".

This isn't always obvious, but the post above is probably close enough.

https://forums.moneysavingexpert.com/discussion/comment/3927376#Comment_3927376 may also be worth a read. Especially the bit about "flat rate" interest.

ahll

AlwaysSomething wrote: »

I don't think you want to use FV. What you are calculating is a bit like this:
"I invest 4012.32, and receive interest every month. How much will I have at the end?" In that example, the 4012 remains invested throughout - that's why you get a lot of interest. When you are repaying a loan, it's different. The amount owing drops each month. At the beginning, you owe 4012.32. After 1 month, you have repaid some of the loan, so the amount on which interest is charged goes down. You end up with a lot less interest.
I did =PMT(0.069/12,35,4012.32,,0) to calculate the monthly payment required to pay off the loan in 35 months. That gave 126.89 (the monthly payment). Multiply that by 35 and you get 4441.08 which is very close to your 4453.

Thank you so much I have got it working. In fact the Nissan dealer was really surprised(more amazed) how accurate my figures where