Intro

It is possible to use a calculator (or even pencil and paper) to do all sorts of calculations with money and interest. However, it is easier to use a spreadsheet application like Microsoft Excel. In this section I fiddle with financial equations, especially the basic financial functions in Excel, for the sake of my own understanding.

My Excel Documents

Here are some Excel documents I made that are useful for doing some financial calculations. Don't forget that some of the formulas require that you activate the Analysis Tools Pack via Tools > Add-Ins.

Asset Payments

Everyone wants their money to be in an asset. You want more money back than what you gave out. In other words: if I borrow $100 now, then I eventually pay back $100 plus interest.

There are many ways to give and return money.

It is worth noting the difference between the following two terms:

Arguments for Excel Financial Functions

Here I shall conceptually explain some of the more common arguments for Excel financial functions. Please note that I will show Excel functions IN ALL CAPS, although the case is actually irrelevant for designating functions in Excel; also, I will show required arguments for Excel functions in bold.

pv
Present Value. Aka the principal. A present sum of money.
rate
Interest RATE per pay period. EG: If the annual interest rate is 12% and payments are made monthly, than the rate = 12% / 12 = 1%.
nper
Total Number of pay PERiods. EG: If the loan is paid monthly for 2 and a half years, then the nper = 2.5 * 12 = 30.  
pmt
PayMenT per pay period. For annuities this is constant and usually includes interest payments.
fv
Future Value. Aka the cash balance. This is usually the pv with the appropriate amount of interest added to it.
type
TYPE of loan. This is whether the payment are due at the end of the pay period (0, the default value); or the due at the beginning of the pay period (1).
per
m pay PERiod out of nper pay periods. EG: If the loan has 10 pay periods, than you can set per = 1, 2, 3, ..., or 10. 
guess
GUESS of what the rate is. Some of the functions use iterative methods to calculate a value. Guess provides a seed to start the iterations. If omitted, guess is assumed to be 10%.
start_period
STARTing pay PERIOD of a range of periods.
end_period
ENDing pay PERIOD of a range of periods.

Excel Functions for Annuities

Here is the basic equation for lump sum payments:

fvLumpSum  =  pv*(1+rate)nper 

Here is the basic equation for annuities where the payment is due at the end of each pay period:

fvAnnuity  =  pmt  *  [   {  (1+rate)nper  -  1)  }  /  rate  ]

The cool thing is that not only does Excel use an equation that combines the two equations above, but also throws in a fix so it can handle annuity payments that occur either at the end or the beginning of a pay period:

fv  =  fvLumpSum  +  fvAnnuity  *  (1  +  rate*type)

The above equation can be solved for any of its variables, resulting in the following five most fundament Excel financial functions.

PV(rate,nper,pmt,fv,type)
Returns the Present Value.
FV(rate,nper,pmt,pv,type)
Returns the Future Value.
RATE(nper,pmt,pv,fv,type,guess)
Returns the interest RATE per pay period.
NPER(rate, pmt, pv, fv, type)
Returns the total Number of pay PERiods.
PMT(rate,nper,pv,fv,type)
Returns the PayMenT per pay period.

Note that bold indicates a required parameter.

Here are additional Excel functions closely tied in to the above functions:

IPMT(rate,per,nper,pv,fv,type)
Returns the Interest PayMenT paid in per pay period out of nper pay periods.
PPMT(rate,per,nper,pv,fv,type)
Returns the Princiapal PayMenT paid in per pay period out of nper pay periods.

These two Excel functions assume that the fv is zero (I don't know why they couldn't figure in fv!):

CUMIPMT(rate,nper,pv,start_period,end_period,type)
Returns the CUMulative Interest PayMenTs paid in a given range of pay periods.
CUMPRINC(rate,nper,pv,start_period,end_period,type)
Returns the CUMulative PRINCipal payments paid in a given range of pay periods.

Other Excel Financial Functions

[The rest of this article is a work in progress.]

Here is a list of all 53 financial functions in Excel, copied directly from the Help for Excel 2000 (ver. 9). After this list I shall try to group these functions by category.

  1. ACCRINT Returns the accrued interest for a security that pays periodic interest
  2. ACCRINTM Returns the accrued interest for a security that pays interest at maturity
  3. AMORDEGRC Returns the depreciation for each accounting period
  4. AMORLINC Returns the depreciation for each accounting period
  5. COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
  6. COUPDAYS Returns the number of days in the coupon period that contains the settlement date
  7. COUPDAYSNC Returns the number of days from the settlement date to the next coupon date
  8. COUPNCD Returns the next coupon date after the settlement date
  9. COUPNUM Returns the number of coupons payable between the settlement date and maturity date
  10. COUPPCD Returns the previous coupon date before the settlement date
  11. CUMIPMT Returns the cumulative interest paid between two periods
  12. CUMPRINC Returns the cumulative principal paid on a loan between two periods
  13. DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
  14. DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
  15. DISC Returns the discount rate for a security
  16. DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
  17. DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
  18. DURATION Returns the annual duration of a security with periodic interest payments
  19. EFFECT Returns the effective annual interest rate
  20. FV Returns the future value of an investment
  21. FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
  22. INTRATE Returns the interest rate for a fully invested security
  23. IPMT Returns the interest payment for an investment for a given period
  24. IRR Returns the internal rate of return for a series of cash flows
  25. ISPMT Calculates the interest paid during a specific period of an investment.
  26. MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100
  27. MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
  28. NOMINAL Returns the annual nominal interest rate
  29. NPER Returns the number of periods for an investment
  30. NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
  31. ODDFPRICE Returns the price per $100 face value of a security with an odd first period
  32. ODDFYIELD Returns the yield of a security with an odd first period
  33. ODDLPRICE Returns the price per $100 face value of a security with an odd last period
  34. ODDLYIELD Returns the yield of a security with an odd last period
  35. PMT Returns the periodic payment for an annuity
  36. PPMT Returns the payment on the principal for an investment for a given period
  37. PRICE Returns the price per $100 face value of a security that pays periodic interest
  38. PRICEDISC Returns the price per $100 face value of a discounted security
  39. PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
  40. PV Returns the present value of an investment
  41. RATE Returns the interest rate per period of an annuity
  42. RECEIVED Returns the amount received at maturity for a fully invested security
  43. SLN Returns the straight-line depreciation of an asset for one period
  44. SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
  45. TBILLEQ Returns the bond-equivalent yield for a Treasury bill
  46. TBILLPRICE Returns the price per $100 face value for a Treasury bill
  47. TBILLYIELD Returns the yield for a Treasury bill
  48. VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method
  49. XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
  50. XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
  51. YIELD Returns the yield on a security that pays periodic interest
  52. YIELDDISC Returns the annual yield for a discounted security. For example, a Treasury bill
  53. YIELDMAT Returns the annual yield of a security that pays interest at maturity

 

Arithmetic Gradient Payments

Arithmetic Gradient Uniform Series

A = G[{(1 + i)n - in - 1} / {i(1 + i)n -1}]

Arithmetic Gradient Present Worth

P = G[{(1 + i)n - in - 1} / {i2(1 + i)n}]

Geometric Gradient Payments

Geometric Series Present Worth (when i = g)

P = A1[n(1 + i)-1]

Geometric Series Present Worth (when i <> g)

P = A1[{1 - (1 + g)n(1 + i)-n} / {i - g}]

Rule of 72

The Rule of 72 is a simple, convenient, and generally accurate rule of thumb:

72/rate = n years to double money if no additional principal is paid.

Lets say that P = principal, r = interest rate, and n = number of years. Then to double your money you need to solve this equation:

P * (1 + r/100)n = 2P

(1 + r/100)n = 2

And since natural logarithms behave in this fashion: ln(ab) = b * ln(a)

n * ln(1 + r/100) = ln(2)

n = ln(2)/ln(1 + r/100)

So if we plug in an interest rate of 8% we get this:

n = ln(2)/ln(1 + 8/100)

n = ln(2)/ln(1.08)

n = 0.693147... / 0.076961...

n = 9.01 years

Here is what the Rule of 72 would yield for 8%:

72/8 = 9.00 years!

Just for the fun of it, this table compares the real equation against the Rule of 72:

Interest Rate Years to Double Difference
Real Rule of 72 Years Percent
0.5 138.98 144.00 5.02 3.62%
1.0 69.66 72.00 2.34 3.36%
1.5 46.56 48.00 1.44 3.10%
2.0 35.00 36.00 1.00 2.85%
2.5 28.07 28.80 0.73 2.60%
3.0 23.45 24.00 0.55 2.35%
3.5 20.15 20.57 0.42 2.10%
4.0 17.67 18.00 0.33 1.85%
4.5 15.75 16.00 0.25 1.60%
5.0 14.21 14.40 0.19 1.36%
5.5 12.95 13.09 0.14 1.12%
6.0 11.90 12.00 0.10 0.88%
6.5 11.01 11.08 0.07 0.64%
7.0 10.24 10.29 0.04 0.40%
7.5 9.58 9.60 0.02 0.16%
8.0 9.01 9.00 0.01 0.07%
9.0 8.04 8.00 0.04 0.54%
9.5 7.64 7.58 0.06 0.77%
10.0 7.27 7.20 0.07 1.00%
10.5 6.94 6.86 0.09 1.23%
11.0 6.64 6.55 0.10 1.45%
12.0 6.12 6.00 0.12 1.90%
13.0 5.67 5.54 0.13 2.34%
14.0 5.29 5.14 0.15 2.78%
15.0 4.96 4.80 0.16 3.22%
20.0 3.80 3.60 0.20 5.31%
25.0 3.11 2.88 0.23 7.28%
30.0 2.64 2.40 0.24 9.16%
40.0 2.06 1.80 0.26 12.62%
50.0 1.71 1.44 0.27 15.77%
75.0 1.24 0.96 0.28 22.49%
100.0 1.00 0.72 0.28 28.00%

Of course the Rule of 72 can also be used in this fashion:

72/ n = r

That is, if I want to double my money in 5 years, then:

72/5 = 14.4% interest rate is needed

Page Modified: (Hand noted: 2007-10-24 19:39:18Z) (Auto noted: 2007-11-17 06:22:09Z)