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.

• Annuities.xls.
• "Given FV" worksheet. Given a PV, rate, loan length, & FV, get a schedule of payments.
• "Given Pmt" worksheet. Given a PV, rate, loan length, & payments, get a schedule of payments & the FV.
• "Find PV" worksheet. Given an annual payment & rate, get a schedule of payments & the PV.
• "Live Off Interest" worksheet. Given a PV, try to live off the interest. In other words, leech off of a lump sum (EG: Lottery winnings).
• DropDownInvoice.xls. A quick and dirty invoice that has a drop down lists of "items" that fills out the item # and item price. See also "Better Invoices for Better Business" [§] for tips on making good invoices.

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.

• A lump sum is one chunk of money.
• An annuity is a contiguous series of payments paid at regular intervals, where the payments are uniform, i.e. equal-sized or constant. EG: $1,$1, $1,$1..
• Arithmetic payments are a contiguous series of payments paid at regular intervals, where the difference between one payment and the next is calculated by adding some constant value, G. Usually the mth payment is (m-1)G. EG: $1,$2, $3,$4.
• Geometric payments are a contiguous series of payments paid at regular intervals, where the difference between one payment and the next is calculated by multiplying by some constant value, g. Usually the mth payment is the first payment multiplied by (1 + g)m-1. EG: $1,$2, $4,$8.
• Variable interest payments are variations of the above, but where the interest rate is not constant.
• Combinations payments are any mix of the above.

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

• ROI (Return On Investment). The money you net on an investment.
• ROR (Rate Of Return). The Return on Investment divided by a length of time.

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

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 • Basic annuity functions 1. CUMIPMT Returns the cumulative interest paid between two periods 2. CUMPRINC Returns the cumulative principal paid on a loan between two periods 3. FV Returns the future value of an investment 4. IPMT Returns the interest payment for an investment for a given period 5. NPER Returns the number of periods for an investment 6. PMT Returns the periodic payment for an annuity 7. PPMT Returns the payment on the principal for an investment for a given period 8. PV Returns the present value of an investment 9. RATE Returns the interest rate per period of an annuity • Just above basic financial functions 1. FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates 2. IRR Returns the internal rate of return for a series of cash flows 3. NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate 4. MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates 5. XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic 6. XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic • Coupon functions 1. COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date 2. COUPDAYS Returns the number of days in the coupon period that contains the settlement date 3. COUPDAYSNC Returns the number of days from the settlement date to the next coupon date 4. COUPNCD Returns the next coupon date after the settlement date 5. COUPNUM Returns the number of coupons payable between the settlement date and maturity date 6. COUPPCD Returns the previous coupon date before the settlement date • Dollar conversions between decimal and fractions 1. DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number 2. DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction • Treasury bill functions 1. TBILLEQ Returns the bond-equivalent yield for a Treasury bill 2. TBILLPRICE Returns the price per$100 face value for a Treasury bill
3. TBILLYIELD Returns the yield for a Treasury bill
• Effective & nominal interest rates
1. EFFECT Returns the effective annual interest rate
2. NOMINAL Returns the annual nominal interest rate
• Security functions.
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. DISC Returns the discount rate for a security
4. DURATION Returns the annual duration of a security with periodic interest payments
5. INTRATE Returns the interest rate for a fully invested security
6. ISPMT Calculates the interest paid during a specific period of an investment.
7. MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100 8. ODDFPRICE Returns the price per$100 face value of a security with an odd first period
9. ODDFYIELD Returns the yield of a security with an odd first period
10. ODDLPRICE Returns the price per $100 face value of a security with an odd last period 11. ODDLYIELD Returns the yield of a security with an odd last period 12. PRICE Returns the price per$100 face value of a security that pays periodic interest
13. PRICEDISC Returns the price per $100 face value of a discounted security 14. PRICEMAT Returns the price per$100 face value of a security that pays interest at maturity
15. RECEIVED Returns the amount received at maturity for a fully invested security
16. YIELD Returns the yield on a security that pays periodic interest
17. YIELDDISC Returns the annual yield for a discounted security. For example, a Treasury bill
18. YIELDMAT Returns the annual yield of a security that pays interest at maturity
• Depreciation functions.
1. AMORDEGRC Returns the depreciation for each accounting period
2. AMORLINC Returns the depreciation for each accounting period
3. DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
4. DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
5. SLN Returns the straight-line depreciation of an asset for one period
6. SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
7. VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method

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

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

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: ) (Auto noted: )