Working out a future loan settement figure

peterg1965

I am about to take a £16000 fixed rate Barcayloan at 6.3% over 72 months. Is there a spreadsheet available from which I can work out the outstanding loan amount and/or settlement figure for each future month up until the repayment date in 6 years time? I have tried to work this out on an Excel spreadsheet but am not sure how the interest is calculated. Any help appreciated.

LewisC_2

I'd also like something similar to this - anyone have any ideas?

h4nym

Folks

I've calculated the payment as £272.02 per month - does that tally?

The function you need in Excel is FV (future value) as follows:

=fv(Rate,Number of payments,payment amount,principal amount)
=fv(.063/12,72,272.02,-16000)

Best thing I'd suggest is that you break out each of the parameters into a separate cell (label it of course so you can see what you're doing!) and then you can mess with the numbers all you want! If you use a different number of payments, this will tell you the outstanding amount after that number of payments. eg at 60 months, there's £3145.86 outstanding. Or, the PMT function will tell you that if you want to pay it off over 60 months, the payment will required will be £316.06.

One thing to note is that the payment and the principal (in this case) need to be opposites - the principal here is negative, the payment is positive. If they're both positive, then it's calculated as a savings rather than loan!

It's much easier in a spreadsheet - PM me and I can email something to you!

H

techno_saver

There's also a website 'whats the cost' which does the difficult bit for you.