Our objective is to prepare two present value tables: PV of a lump sum, and PV of an annuity.

Excel has many present value functions, although you can also get great results just

using formulas.

G ity just 10% 2 Make Your Own Present Value Table Our objective is to prepare two present value tables: PV of a lump sum, and PV of an annu 4 5 Excel has many present value functions, although you can also get great results 6 using formulas. 7 8 PV OF $1.00 =1/(1+r)^n Use this formula for PV of $1.00 9 Format for 4 digits 10 11 12 PRESENT VALUE OF $1.00 13 Periods 4.00% 5% 6% 8% 12% 14 1 0.9615 15 2 0.9246 16 3 0.8890 17 4 0.8548 18 19 1. Put a heading on your PV table-Present Value of $1.00 (the lump sum table) 20 2. Choose what interest rates you want. 21 3. You can enter periods 1 and 2 select both with a mouse; drag fil handle 22 down the page. 23 4. The formula in D14 is: = 1/(1+$D$13 C14 24 The formula in D15 is: 1/(1+$D$137C15 25 The formula in D16 is: 21/(1+$D$13Y"C16 26 27 PRESENT VALUE OF A SERIES OF $1.00 CASH FLOWS 28 Periods 4.00% 4% 6% 8% 10% 12% 29 0.9615 30 2 1.8861 31 3 2.7751 32 4 3.6299 33 34 In Cell D29 35 =PV($D$28,629,-1) 36 Note: the minus oma the evaluea positive number 37 38 It would be a good idea to compare your tables with these check figures: 39 4% 5% 6% 8% 5% 40 6% 0.9615 0.9524 0.9434 0.9259 0.9615 41 0.9524 0.9434 0.9246 0.9070 0.8900 0.8573 42 1.8861 1.8594 0.8890 0.8638 1.8334 0.8396 0.7938 2.7751 0.8548 2.7232 2.6730 0.8227 0.7921 0.7350 3.6299 0.8219 3.5460 0.7835 0.7473 3.4651 0.6806 4.4518 45 You can choose whatever range of interest rates interests you. This is 4.3295 4.2124 46 particularly helpful if you are testing the sensitivity of a loan payment with 47 respect to an anticipated interest rate, for example 8% 0.9259 1.7833 2.5771 3.3121 3.9927