NPV Calculation - Coffee & Excel.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L
1	NPV Calculation - Coffee & Excel
2
3	Discount Factor	10%
4
5	Annual Cashflows	20,000
6
7	Number Of Years	10
8
9	Year	-	1	2	3	4	5	6	7	8	9	10
10
11	Undiscounted Cashflows	-	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000
12
13	Discount Factor	0	0.9091	0.8264	0.7513	0.6830	0.6209	0.5645	0.5132	0.4665	0.4241	0.3855
14
15	Present Value (Manual)	-	18,182	16,529	15,026	13,660	12,418	11,289	10,263	9,330	8,482	7,711
16
17	Discounted Amount (Manual)	-	1,818	3,471	4,974	6,340	7,582	8,711	9,737	10,670	11,518	12,289
18
19	Present Value (Manually)	$ 122,891.34
20
21	Present Value (NPV Function)	$ 122,891.34
22
23	Adjusting Values:
24	Change the discount rate, modify the percentage in cell B3.
25	Change the undiscounted cashflows, simply update the values in B5. The subsequent calculations will update automatically.
26
27	Understanding the Rows:
28	Undiscounted Cashflows: These are the projected cashflows without any discounting.
29	Discount Factor: This is the factor by which cashflows are discounted for each year, based on the discount rate.
30	Present Value (Manual): This is the present value of the cashflows calculated manually using the discount factor.
31	Present Value (NPV Function): This is the present value of the cashflows calculated using Excel's NPV function.
32	Discounted Amount (Manual): This is the difference between the undiscounted cashflow and its present value for each year.
33
34	Expanding the Table: If you wish to expand the table for more years:
35	Copy the last column and paste it to the next column.
36	Update the year in the header.
37	Adjust the formulas in the new column to reference the correct cells.
38
39	Final Note: Always make sure to double-check your calculations, especially if you make any changes to the default values or formulas.
40
41	List of Formulas and Their Explanations:
42
43	Discount Factor Formula: =1/(1+r)^n
44	Explanation: This formula calculates the discount factor for each year. Here, r represents the discount rate (in this case, 8% or 0.08) and n represents the year. The formula determines how much a future cash flow is worth in today's terms.
45
46	Present Value (Manual) Formula: =Undiscounted Cashflow * Discount Factor
47	Explanation: This formula calculates the present value of a future cash flow by multiplying the undiscounted cash flow by the discount factor for that year. It gives the value of the future cash flow in today's terms.
48
49	Present Value (NPV Function) Formula: =NPV(discount rate, range of cashflows)
50	Explanation: This formula uses Excel's built-in NPV function to calculate the present value of a series of future cash flows. The function takes in the discount rate and a range of cash flows as arguments. Note that in our table, this formula is applied year by year, which is not the typical use of the NPV function. Normally, the NPV function would give the net present value of all future cash flows.
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100