Re: Excel and Finance
Rav00,
The "Excel for Dummies" book is great from going from a basic understanding of excel to a more advanced level.
I don't know if it covers the multitude of financial functions that excel provides as standard functionality.
They best way I learned how to use excel was to "play" with it. In other words I tried each function to see what it gave me as a result.
A common function used in finances is call NPER, which produces the NUMBER OF PAYMENTS required to pay a loan if you knew the principal value, the payment and the interest rate.
An even better one is the PMT function. This would calculate the PAYMENT on a loan if you knew the Principal Value, the Rate, and the Number of Payments.
To try this out.. type in the following
In Cell A1 put in the rate. =6.5%/12 (you need the /12 because 6.5% is an ANNUAL RATE and you're paying over 12 months). You need the = sign as well.
In Cell B1 put in the loan amount. $250,000
In Cell C1 put in the number of payments. 360 for a 30 year mortgage.
In Cell D1 is where the PMT function goes.
Type in =PMT
You might see a tool tip popup showing your what the function wants. In this case it is =PMT(rate, nper, pv, fv, type) This means it wants a RATE, a number of payments, a principal value, a future value, and a payoff type)
To give it what it wants Cell D1 should have this formula: =PMT(B1, C1, A1, 0, 1) The future value is 0 because a paid off loan would have a 0 value when it's over. The type is 1 which means the first payment occurs after interest is charged for the first month,
After you type in the formula.. you get an answer. In this case with a 6.5% Rate, a $250000 Principal Value, and 360 months.. you get.... $1572 for your payment.
In mortgage terms this represents only the Principal and Interest Payment.
Now if you change any of the numbers.. it will change the result as well.
Have Fun! Hope this helps!
|
07-15-2010, 10:41 PM
