Working with Financial Functions. Cost of a loan to the borrower is largely based on three factors: Principal : amount of money being loaned Interest: amount added to the principal by the lender Calculated as simple interest or as compound interest Time required to pay back the loan.
Excel Functions are Excel Functions
To use them, you must understand the
TIME VALUE OF MONEY
Money will increase in value over time if the money is invested and can make more money.
If you have $1,000 today, it will be worth more tomorrow if you invest that $1,000 and it earns additional money (interest or some other return on that investment).
If you have $1,000 today, it will NOT be worth more tomorrow if you put it in an envelope and hide it in a drawer. Then the time value of money does not apply as an increase. It will most likely decrease in value because of inflation. Of course, you won’t lose the whole $1,000 either…
Assume that you have $1,000 to invest. $1,000 is the present value (PV) of your money.
You can invest it and receive “simple” interest or you can earn “compound” interest.
The money that you have at the end of the time you have invested it is called the “future value” (FV) of your money.
Simple interest is always calculated on the initial $1,000. 5% interest on $1,000 is $50. Always $50.
When interest is paid on not only the principal amount invested, but also on any previous interest earned, this is called compound interest.
FV = Principal + (Principal x Interest)
= 1000 + (1000 x .05)
= 1000 (1 + i)
= PV (1 + i)
$1,000 Invested at 5% return
FV(rate, nper, pmt, [pv], [type])
PV(rate, nper, pmt, [fv], [type])
PMT(rate, nper, pv, [fv], [type])
If you borrow money, the lender wants to earn “compound” money on his/her/its investment.
If you borrow $1000 at 10%, then you won’t pay back just $1,100 (unless you pay it back at once during the initial time period).
You will pay it back “compounded”. Interest will be calculated each period on your remaining balance.
What would that same amortization table (also called a schedule) look like if the interest was compounded AFTER you paid, rather than BEFORE you paid?
(this is a type 1 on Excel financial functions)
IPMT(rate, per, nper, pv, [fv], [type])
PPMT(rate, per, nper, pv, [fv], [type])
CUMIPMT calculates the cumulative interest between two periods
CUMPRINC calculates the cumulative principal between two periods
The arguments to both functions are the same
Functions require the analysis tool pack add-in
ALL 6 ARGUMENTS ARE REQUIRED, SCROLL DOWN TO SEE TYPE!
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPPMT(rate, nper, pv, start_period, end_period, type)