Question

Construct a table and a graph showing the relationship between interest rates, time, and present value by showing how $5,000 pushed a year further into the future over a 30-year period would be discounted at different interest rates. Use $5,000 for your future value and calculate the present value of this $5,000 each year over the 30-year period 0%, 2%, 4%, 7%, 10%, 13%, 16%, and 20% compounded annually. Present Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of present values.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

I J K L M N O P Q R S T U V W Year 0% -70 - 0% - 2% - 4% - 7% - 10% 13% - 16% - 20% 3 $6,000.00 4 7 $5,000.00 10 11 12 13 14 15 $4,000.00 16 17 A B C D E F G H Present value at year end at given interest rate 7% 10% 13% 10 $5,000.00 $2,760.35 $1,541.59 $656.84 $286.54 $127.83 1 $5,000.00 $2,815.56 $1,603.26 $702.81 $315.20 $144.44 2 $5,000.00 $2,871.87 $1,667.39 $752.01 $346.72 $163.22 | 3 $5,000.00 $2,929.31 $1,734.08 $804.65 $381.39 $184.44 4 $5,000.00 $2,987.90 $1,803.45 $860.98 $419.53 $208.42 5 $5,000.00 $3,047.65 $1,875.58 $921.25 $461.48 $235.51 6 $5,000.00 $3,108.61 $1,950.61 $985.73 $507.63 $266.13 | 7 $5,000.00 $3,170.78 $2,028.63 $1,054.73 $558.39 $300.72 8 $5,000.00 $3,234.20 $2,109.78 $1,128.57 $614.23 $339.82 9 $5,000.00 $3,298.88 $2,194.17 $1,207.57 $675.65 $383.99 10 $5,000.00 $3,364.86 $2,281.93 $1,292.10 $743.22 $433.91 11 $5,000.00 $3,432.15 $2,373.21 $1,382.54 1,382.54 $817.54 $490.32 12 $5,000.00 $3,500.80 $2,468.14 $1,479.32| $899.29 $554.06 13 $5,000.00 $3,570.81 $2,566.87 $1,582.87| $989.22 $626.09 | 14 | $5,000.00 $3,642.23 $2,669.54 $1.693.67| $1.088.15 $707.48 15 $5,000.00 $3,715.07 $2,776.32 $1,812.23 $1,196.96 $799.45 16 $5,000.00 $3,789.38 $2,887.38 $1,939.09 $1,316.66 $903.38 17 $5,000.00 $3,865.16 $3,002.87 $2,074.82 $1,448.32 $1,020.82 18 $5,000.00 $3,942.47 $3,122.99 $2,220.06 $1,593.15 $1,153.53 19 $5,000.00 $4,021.32 $3,247.90 $2,375.46 $1,752.47| $1,303.49 20 $5,000.00 $4,101.74 $3,377.82 $2,541.75 $1,927.72 $1,472.94 21 $5,000.00 $4,183.78 $3,512.93 $2,719.67 $2,120.49 $1,664.42 22 $5,000.00 $4,267.45 $3,653.45 $2,910.05 $2,332.54 $1,880.80 | 23 $5,000.00 $4,352.80 $3,799.59 $3,113.75 $2,565.79 $2,125.30 | 24 $5,000.00 $4,439.86 $3,951.57 $3,331.71 $2,822.37 $2,401.59 | 25 $5,000.00 $4,528.65 $4,109.64 $3,564.93 $3,104.61 $2,713.80 26 $5,000.00 $4,619.23 $4,274.02 $3,814.48 $3,415.07 $3,066.59 | 27 $5,000.00 $4,711.61 $4,444.98 $4,081.49 $3,756.57 $3,465.25 28 $5,000.00 $4,805.84 $4,622.78 $4,367.19 $4,132.23 $3,915.73 | 29 $5,000.00 $4,901.96 $4,807.69 $4,672.90 $4,545.45 $4,424.78 30 $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00 16% $58.24 $67.56 $78.37 $90.91 $105.45 $122.33 $141.90 $164.60 $190.94 $221.49 $256.93 $298.04 $345.72 $401.04 $465.201 $539.64 $625.98 $726.13 $842.31 $977.08 $1,133.42 $1,314.76 $1,525.13 $1,769.15 $2,052.21 $2,380.57 $2,761.46 $3,203.29 $3,715.81 $4,310.34 $5,000.00 20% $21.06 $25.28 $30.33 $36.40 $43.68 $52.41 $62.90 $75.47 $90.57 $108.68 $130.42 $156.50 $187.81 $225.37 $270.44 $324.53 $389.43 $467.32 $560.78 $672.94 $807.53 $969.03 $1,162.84 $1,395.41 $1,674.49 $2,009.39 $2,411.27 $2,893.52 $3,472.22 $4,166.67 $5,000.00 18 20 PRESENT VALUE $3,000.00 23 24 $2,000.00 26 27 28 30 31 $1,000.00 32 33 I $0.00 30 35 152025 YEARS

Cell reference -

A 5 6 7 8 10 12 16 B D Present value at year end at given interest rate Year o 0.02 0.04 0.07 0.1 0.13 0.16 0.2 0 =5000/(1+$2)^($B$33-$B3) =5000/(1+D$2)^($B$33-$B3) =5000/(1+E$2)^($B$33-$B3) =5000/(1+F$2)^($B$33-$B3) =5000/(1+G$2)^($B$33-$B3) =5000/(1+H$2)^($B$33-$B3) =5000/(1+I$2)^($B$33-$B3) =5000/(1+$2)^($B$33-$B3) =5000/(1+C$2)^($B$33-$B4) =5000/(1+D$2)^($B$33-$B4) =5000/(1+E$2)^($B$33-$B4) =5000/(1+F$2)^($B$33-$B4) =5000/(1+G$2)^($B$33-$B4) =5000/(1+H$2)^($B$33-$B4) =5000/(1+$2)^($B$33-$B4) =5000/(1+$2)^($B$33-$B4) 2 =5000/(1+$2)^($B$33-$B5) =5000/(1+D$2)^($B$33-$B5) =5000/(1+E$2)^($B$33-$B5) =5000/(1+F$2)^($B$33-$B5) =5000/(1+G$2)^($B$33-$B5) =5000/(1+H$2)^($B$33-$B5) =5000/(1+I$2)^($B$33-$B5) =5000/(1+$2)^($B$33-$B5) 3 =5000/(1+C$2)($B$33-B6) 1=5000/(1+D$2)^($B$33-$B6) 1=5000/(1+$2)^($B$33-$36) =5000/(1+F$2)^($B$33-$B6) 1=5000/(1+G$2)^($B$33-$B6) =5000/(1+H$2)^($B$33-$B6) =5000/(1+I$27($B$33-$B6) 1=5000/(1+$2)^($B$33-$B6) =5000/(1+C$2)^($B$33-$B7) =5000/(1+D$2)^($B$33-$B7) =5000/(1+E$2)^($B$33-$B7) =5000/(1+F$2)^($B$33-$B7) =5000/(1+G$2)^($B$33-$B7) =5000/(1+H$2)^($B$33-$37) =5000/(1+$2)^($B$33-$B7) =5000/(1+$2)^($B$33-$B7) =5000/(1+C$2)^($B$33-$B8) =5000/(1+D$2)^($B$33-$B8) =5000/(1+$2)^($B$33-$B8) =5000/(1+F$2)^($B$33-$B8) =5000/(1+G$2)^($B$33-$B8) =5000/(1+H$2)^($B$33-$B8) =5000/(1+$2)^($B$33-$B8) =5000/(1+J$2)^($B$33-$B8) =5000/(1+$2)^($B$33-$89) =5000/(1+D$2)^($B$33-$B9) =5000/(1+$2)^($B$33-$B9) =5000/(1+F$2)^($B$33-$B9) =5000/(1+G$2)^($B$33-$B9) =5000/(1+H$2)^($B$33-$89) =5000/(1+$2)^($B$33-$B9) =5000/(1+J$2)^($B$33-$89) =5000/(1+$2)^($B$33-$B10) =5000/(1+D$2)^($B$33-$B10) =5000/(1+E$2)^($B$33-$B10) =5000/(1+F$2)($B$33-$B10) =5000/(1+$2)^($B$33-$B10) =5000/(1+H$2)^($B$33-$310) =5000/(1+$2)^($B$33-$B10) =5000/(1+J$2)^($B$33-$B10) =5000/(1+C$2)^($B$33-$B11) =5000/(1+D$2)^($B$33-$B11) =5000/(1+E$2)^($B$33-$B11) =5000/(1+F$2)^($B$33-$811) =5000/(1+G$2)^($B$33-$B11) =5000/(1+H$2)^($B$33-$811) =5000/(1+I$2)^($B$33-$B11) =5000/(1+$2)^($B$33-$B11) =5000/(1+C$2)^($B$33-$B12) =5000/(1+D$2)^($B$33-$B12) =5000/(1+E$2)^($B$33-$B12) =5000/(1+F$2)^($B$33-$B12) =5000/(1+G$2)^($B$33-$B12) =5000/(1+H$2)^($B$33-$B12) =5000/(1+$2)^($B$33-$B12) =5000/(1+$2)^($B$33-$B12) =5000/(1+C$2)^($B$33-$B13) =5000/(1+D$2)^($B$33-$B13) =5000/(1+E$2)^($B$33-$B13) =5000/(1+F$2)^($B$33-$B13) =5000/(1+G$2)^($B$33-$B13) =5000/(1+H$2)^($B$33-$B13) =5000/(1+$2)^($B$33-$B13) =5000/(1+$2)^($B$33-$B13) =5000/(1+C$2)^($B$33-$B14) =5000/(1+D$2)^($B$33-$B14) =5000/(1+E$2)^($B$33-$B14) =5000/(1+F$2)^($B$33-$B14) =5000/(1+G$2)^($B$33-$B14) =5000/(1+H$2)^($B$33-$B14) =5000/(1+$2)^($B$33-$B14) =5000/(1+$2)^($B$33-$B14) =5000/(1+$2)^($B$33-$B15) =5000/(1+D$2)^($B$33-$B15) =5000/(1+E$2)^($B$33-$B15) =5000/(1+F$2)^($B$33-$B15) =5000/(1+$2)^($B$33-$B15) =5000/(1+H$2)^($B$33-$B15) =5000/(1+$2)^($B$33-$B15) =5000/(1+$2)^($B$33-$B15) 13 =5000/(1+C$ 2)^($B$33-$B16) =5000/(1+D$2)^($B$33-$B16) =5000/(1+E$2)^($B$33-$B16) =5000/(1+F$2)^($B$33-$B16) =5000/(1+G$2)^($B$33-$B16) =5000/(1+H$2)^($B$33-$B16) =5000/(1+$2)^($B$33-$B16) =5000/(1+$2)^($B$33-$B16) 14 =5000/(1+C$2)^($B$33-$B17) =5000/(1+D$2)^($B$33-$B17) =5000/(1+$2)^($B$33-$B17) =5000/(1+F$2)^($B$33-$B17) =5000/(1+G$2)^($B$33-$B17) =5000/(1+H$2)^($B$33-$B17) =5000/(1+$2)^($B$33-$B17) =5000/(1+J$2)^($B$33-$B17) 15 =5000/(1+$2)^($B$33-$B18) =5000/(1+D$2)^($B$33-$B18) =5000/(1+E$2)^($B$33-$B18) =5000/(1+F$2)^($B$33-$B18) =5000/(1+G$2)^($B$33-$B18) =5000/(1+H$2)^($B$33-$B18) =5000/(1+I$2)^($B$33-$B18) =5000/(1+$2)^($B$33-$B18) =5000/(1+C$2)^($B$33-$B19) =5000/(1+D$2)^($B$33-$B19) =5000/(1+E$2)($B$33-$B19) =5000/(1+F$2)^($B$33-$B19) =5000/(1+G$2)^($B$33-$819) =5000/(1+H$2)^($B$33-$B19) =5000/(1+1$2)^($B$33-$B19) =5000/(1+$2)^($B$33-$B19) 17 =5000/(1+$2)^($B$33-$B20) =5000/(1+D$2)^($B$33-$B20) =5000/(1+E$2)^($B$33-$B20) =5000/(1+F$2)^($B$33-$B20) =5000/(1+G$2)^($B$33-$B20) =5000/(1+H$2)^($B$33-$B20) =5000/(1+$2)^($B$33-$B20) =5000/(1+$2)^($B$33-$B20) 18 =5000/(1+C$2)^($B$33-$B21) =5000/(1+D$2)^($B$33-$B21) =5000/(1+E$2)^($B$33-B21) =5000/(1+F$2)^($B$33-$B21) =5000/(1+G$2)^($B$33-$B21) =5000/(1+H$2)^($B$33-B21) =5000/(1+$2)^($B$33-$B21) =5000/(1+$2)^($B$33-B21) 19 =5000/(1+$2)^($B$33-$B22) =5000/(1+D$2)^($B$33-$B22) =5000/(1+$2)^($B$33-$B22) =5000/(1+F$2)^($B$33-$B22) =5000/(1+$2)^($B$33-$B22) =5000/(1+H$2)^($B$33-$B22) =5000/(1+$2)^($B$33-$B22) =5000/(1+J$2)^($B$33-$B22) 20 =5000/(1+$2)^($B$33-$B23) =5000/(1+D$2)^($B$33-$B23) =5000/(1+E$2)^($B$33-$B23) =5000/(1+F$2)($B$33-$B23) =5000/(1+$2)^($B$33-$B23) =5000/(1+H$2)^($B$33-$B23) =5000/(1+$2)^($B$33-$B23) =5000/(1+$2)^($B$33-$B23) 21 =5000/(1+C$2)^($B$33-$B24) =5000/(1+D$2)^($B$33-$B24) =5000/(1+E$2)^($B$33-$B24) =5000/(1+F$2)^($B$33-$B24) =5000/(1+$2)^($B$33-$B24) =5000/(1+H$2)^($B$33-$B24) =5000/(1+$2)^($B$33-$B24) =5000/(1+$2)^($B$33-$B24) 22 =5000/(1+$2)^($B$33-$B25) =5000/(1+D$2)^($B$33-$B25) =5000/(1+E$2)^($B$33-$B25) =5000/(1+F$2)^($B$33-$B25) =5000/(1+G$2)^($B$33-$B25) =5000/(1+H$2W($B$33-6B25) =5000/(1+I$2)^($B$33-$B25) =5000/(1+J$2)^($B$33-$B25) 23 =5000/(1+$2)^($B$33-$326) =5000/(1+D$2)^($B$33-$326) =5000/(1+E$2)^($B$33-$B26) =5000/(1+F$2)^($B$33-$B26) =5000/(1+$2)^($B$33-$326) =5000/(1+H$2)^($B$33-$326) =5000/(1+$2)^($B$33-$B26) =5000/(1+J$2)^($B$33-$B26) 24 =5000/(1+$2)^($B$33-$B27) =5000/(1+D$2)^($B$33-$B27) =5000/(1+E$2)^($B$33-$B27) =5000/(1+F$2)^($B$33-$B27) =5000/(1+$2)^($B$33-$B27) =5000/(1+H$2)^($B$33-$B27) =5000/(1+$2)^($B$33-$B27) =5000/(1+J$2)^($B$33-$B27) 25 =5000/(1+$2)^($B$33-$B28) =5000/(1+D$2)^($B$33-$B28) =5000/(1+E$2)^($B$33-$B28) =5000/(1+F$2)^($B$33-$B28) =5000/(1+G$2)^($B$33-$B28) =5000/(1+H$2)^($B$33-$B28) =5000/(1+$2)^($B$33-$B28) =5000/(1+$2)^($B$33-$B28) =5000/(1+C$2)^($B$33-$B29) =5000/(1+D$2)^($B$33-$B29) =5000/(1+$2)^($B$33-$B29) =5000/(1+F$2)^($B$33-$B29) =5000/(1+G$2)^($B$33-$B29) =5000/(1+H$2)^($B$33-$B29) =5000/(1+$2)^($B$33-$329) =5000/(1+$2)^($B$33-$B29) 27 =5000/(1+C$2)^($B$33-$B30) =5000/(1+D$2)^($B$33-$B30) =5000/(1+E$2)^($B$33-$B30) =5000/(1+F$2)^($B$33-$B30) =5000/(1+$2)^($B$33-$B30) =5000/(1+H$2)^($B$33-$B30) =5000/(1+$2)^($B$33-$B30) =5000/(1+$2)^($B$33-$B30) 28 =5000/(1+C$2)^($B$33-$B31) =5000/(1+D$2)^($B$33-$B31) =5000/(1+E$2($B$33-$B31) =5000/(1+F$2)^($B$33-$B31) =5000/(1+G$2)^($B$33-$B31) =5000/(1+H$2)^($B$33-$B31) =5000/(1+$2)^($B$33-$B31) =5000/(1+$2)($B$33-$B31) =5000/(1+$2)^($B$33-$B32) =5000/(1+D$2)^($B$33-$B32) =5000/(1+$2)^($B$33-$B32) =5000/(1+F$2)^($B$33-$B32) =5000/(1+G$2)^($B$33-$B32) =5000/(1+H$2/1($B$33-$B32) =5000/(1+I$2)^($B$33-$B32) =5000/(1+J$2)^($B$33-$B32) 30 =5000/(1+$2)^($B$33-$B33) =5000/(1+D$2)^($B$33-$B33) =5000/(1+$2)^($B$33-$B33) =5000/(1+F$2)^($B$33-$B33) =5000/(1+G$2)^($B$33-$B33) =5000/(1+H$2)^($B$33-$B33) =5000/(1+$2)^($B$33-$B33) =5000/(1+J$2)^($B$33-$B33) 18 16 5500 20 21 23 25 28 30 29

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.