Hi folks… Last question for today… I promise. :-p
I find I’m often using online loan calculators and I’d like to be able to do this myself just using a spreadsheet.
If I’ve got all the necessary figures such as Loan Amount, Deposit, Interest Rate, Loan Term In Years – does anyone know the formula used to give the minimum fortnightly/monthly repayment?
Allan.
In Excell, use the PMT function
PMT =(rate,Nper,Pv,Fv,type)
rate = interest rate / payments for year
Nper = total number of repayments for life of loan
Pv = Loan amount value
Fv = Future Value
type = logic value, pay at end of loan = 0, beginning = 1So, if you had a 6.5%, $100000 loan paid monthly and interest calculated monthly over 25 years and you want to work out the monthly payment(PMT), then you put the following into your excell fields.
rate = 6%/12 = 0.005
Nper = 25 * 12 = 300
Pv = 100,000
Fv = 0
type = 0 (usually)PMT = -$644 per month (notice the minus sign to indicate it is a cash outflow.
I only just learnt this the other day, But I think this gives a basic overview.
Do a search with excell help on PMT.
Live, Learn and GrowLifexperience
Thank you – for making my head hurt… [blush2] I’ll do some helpfile reading!
Allan.
You must be logged in to reply to this topic. If you don't have an account, you can register here.
