Question: How much is the monthly payment for a 5-year $20,000 car loan at 6.5% interest? Answer: $391.32.
There is a really simple excel function that will get you to that answer. It is the PMT function.
PMT(rate, nper, pv, [fv], [type])
The PMT function takes 3 required arguments and 2 optional arguments.
Arguments:
- rate: the interest rate. Make sure you have the right interest rate for the period. Most rates are quoted on an annual basis, so you'll most likely have to divide the yearly interest rate by 12. So 6.50%/12
- nper: the number of periods. Once again the interest rate and period need to match, if it's a five year loan then the number of periods is equal to 60 months.
- pv: present value of the loan/face value of the loan. $20,000 loan so pv = $20,000.
- [fv]: Optional argument, but it represents the future value of the loan. Since you are paying the loan off the future value is zero, but if you leave it out excel assumes that it is zero.
- [type]: Optional argument, takes a value of 0 or 1. 0 means you pay at the beginning of the period (of the month) and 1 means the end of the period (month).
Here is a screen-shot:
The formula is: "=-PMT($B$2/12,$B$3,$B$1)"
B2 contains the interest rate
B3 is the number of months and
B1 is the loan amount.
The negative sign is to make the number positive.
So that's how you use PMT. Next time, we will put together an amortization table in excel to see how extra payments affect our scheduled payoff date.
Tags: personal finance excel
2 comments:
I like FV. Tells me how much I will have rather than how much I will owe! :)
I don't think that is the right way to transform yearly rates into monthly (simply dividing by 12).. although it is the way Prosper does it...
A simple calculation proves that method is wrong:
PV=$100
n=1
i=12% yearly
------------
FV=$112,00
now... converting by your manner, 12%y=1%montly
PV=$100
n=12
i=1%
----------
FV=112,68
That's a small change, but it's 100 bucks... now take it to account when considering millions of dollars (or maybe thousands, if you're very concerned about your money! :))
I learned in Biz school that you should consider the compound factor... so the formula would be:
(1+i)^12 = (1+x)
where (i) is the monthly rate and (x) the yearly.
So a 12% annual rate would be a 0,9488% monthly.
An easy way to do it (if you have a financial calculator nearby) is to use these values:
FV=(1 + yearly rate) (ex: 12% = 1.12)
PV= -1
n=12
---------
i= monthly rate (0.9488%)
Wow... long comment.. but I hope it helps.
Post a Comment