Accounting Excel
Sheet1
Alt A | Alt B | Alt C | Alt D | |||||||||||||
Year | Cash flow | P | A | S | Row Total | P | A | S | Row Total | P | A | S | Row Total | |||
0 | 0 | -50000 | -50000 | -95000 | -95000 | -350000 | -350000 | |||||||||
1 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
2 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
3 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
4 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
5 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
6 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
7 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
8 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
9 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
10 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
11 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
12 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
13 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
14 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
15 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
16 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
17 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
18 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
19 | 0 | 5100 | 5100 | 10500 | 10500 | 36000 | 36000 | |||||||||
20 | 0 | 5100 | 30000 | 35100 | 10500 | 30000 | 40500 | 36000 | 150000 | 186000 | ||||||
NPW | 0 | ($2,121.52) | ($1,148.27) | ($21,215.16) | ||||||||||||
($1,148.27) |
Use the NPV function in EXCEL to find the NPW of all but year 0. Then subtract the initial investment to get NPW as we know it: =NPV(0.1,G4:G23)+G3 Notice that this value is negative, so the alternative is undesirable
Alternative A (the “Do nothing” alternative) had the only NPW greater than or equal to zero, so it is the preferred alternative for this problem.
Use the summation function in the menu bar (Greek letter capital sigma) to get the row total. Then drag the cell corner to extend it to the entire column.
Since this is a benefit, the sign for the uniform series is positive. If these were costs, the sign would be negative.
The Salvage value is a Future Value (F or FV).
Note simpler approach using PV function and including the payment and salvage values for pmt and fv: -95000 – PV(0.1,20,10500,30000)