Net Present Value Function in Excel

In a studying frenzy for my CMA case exam, I came up with an NPV template that would tackle:
- changing tax rates
- varying annual cash in-flows & out-flows

In a 4 hour exam, time is precious. I won't have time to use my financial calculator if they gave us a timeline of more than 5 years, which happened in the Solare Consulting case. Hopefully this blog post will be helpful for those who are looking for a quick and dirty reference on calculating NPV for capital budgeting exercise.

Disclaimer: My template has not been audited for accuracy. Use at your own discretion.
Here is the fore-mentioned template. Link: https://www.dropbox.com/s/9rs0tywhmvm50qf/Quant%20Template.docx
I tested it against using the PV factor formula, where-by: (1+k)^-n
I have n laid out as a horizontal timeline: 0 1 2 3 .... n on Excel, where these numbers are linked to, when finding what the PV factor for each n-th year is. (Note: you can copy and paste horizontally until time_n once the PV factor formula is set up)

Then, take the net after-tax annual cash-flows per each year and multiply it by the respective PV factor for that n-th year. Take the sum of these discounted cash-flows. Bang, you've got your NPV.

That is using financial mathematics.

However, there is a quicker way yet.
Find the sum of all your after-tax dollars in the column when n = 0; let's call this CF_0. Then, add that CF_0 with NPV(k%, CF_1:CF_n)

where k% is your after-tax WACC (Weighted Average Cost of Capital).

I tested both and they match.

If the exam gives varying tax rates for different EBIT levels, I might cry a little and use if-statements on Secure Exam to insert tax rates. Then, find NPV. Alright, I have anticipated as many curve-balls they can throw at me. I just hope I'm ready!!