Question

Net Present Value of Payouts The NPV of a series of values is calculated using a discount rate applied to those values. Apply the NPV function to the series of expected benefit payouts using .075 as the discount rate. You can hard-code the discount rate. How would you calculate this in Excel? Please show calculation and logic used in excel, so I understand. Below are the values:

Year		Expected Benefit Payouts
2016		NA
2017		$19,661,882,100.00
2018		$19,909,120,629.23
2019		$20,162,051,346.58
2020		$20,420,680,083.92
2021		$20,685,015,994.25
2022		$20,955,071,470.68
2023		$21,230,862,069.61
2024		$21,512,406,437.74
2025		$21,799,726,242.91
2026		$22,092,846,108.56
2027		$22,391,793,551.78
2028		$22,696,598,924.58
2029		$23,007,295,358.62
2030		$23,323,918,712.87
2031		$23,646,507,524.45
2032		$23,975,102,962.33
2033		$24,309,748,783.82
2034		$24,650,491,293.79
2035		$24,997,379,306.50
2036		$25,350,464,110.00
2037		$25,709,799,432.87
2038		$26,075,441,413.41
2039		$26,447,448,571.04
2040		$26,825,881,779.94
2041		$27,210,804,244.76
2042		$27,602,281,478.45
2043		$28,000,381,281.99
2044		$28,405,173,726.15
2045		$28,816,731,135.01
2046		$29,235,128,071.33

Answer 1

In the above solution, PV of cash flows at both 2015 and 2016 are considered.

Note: Please let me know if any doubt on the formulae given below.

Formulae