Excel File for the NPV Template - Now Available

The excel file for the NPV Template is available for download for $4.99. It is an Excel 2007 file (.xlsx). The file will be emailed within 24 hours of receipt of payment, to the email address identified (please remember to include your email address in the message).

To purchase the excel file, please send an Email Money Transfer or an Amazon.ca e-gift card for $4.99 to the following email address: cmacasemaster@gmail.com

Calculating Present Value, Future Value and Payment In Excel - Formulas

 

When writing the CMA Case Exam or the CMA Board Report you can (and should) use formulas to help you compute Present, Future and Payment values. After posting my NPV Template where I utilize these formulas, I have received some email questions about setting up these Time Value of Money formulas (present value, future value and payment) in excel. I put the above together so that you can see how I calculate these values in excel, using formulas. Let me know if you have any questions on the above.

Notes:

• rate is the discount rate per period
• nper is the number of periods
• PMT is the payment per period
• PV is the present value
• type indicates whether it is a regular annuity or an annuity due (whether payment is due at the end of a period or at the beginning)
• FV is future value

If you have other questions on excel (pivot tables, formulas, formating, etc. post them in the comments and I will do a post on these).

CM

NPV Tips and Template

~This template is now available for download in excel format so that you can see all the links and formulas: NPV Template ~

~This template is now available for download in excel format so that you can see all the links and formulas: NPV Template ~

This is the template that I used during the CMA Case Exam. I typed it in quickly for the first alternative and then copied and pasted it for all the other alternatives. This way, as I was approaching each alternative, I just had to fill in the data and all the calculations were already set up. This helped me streamline and speedup the (Net Present Value) NPV process and to not forget any of the NPV components. 

Below is a discussion of each of the above components as well as some general guidelines for NPV calculations:

General notes:

• Unless otherwise suggested, calculate a 5 year NPV with deemed disposition at year 5 (hence the salvage value at year 5). 
• Try not to calculate anything using the calculator. Use the securexam's mock excel so that if you need to change assumptions or fix errors all you have to do is change one number and everything gets recalculated for you.
• Remember to use incremental after tax cash flows.
• Financing costs are not relevant to NPV and should not be included (ie. the interest that will have to be paid for the line of credit that will fund the investment is not something that is relevant to the NPV calculation).
• Always state all of your assumptions clearly and show an audit trail.

Discussion of 'a' through 'f':
Note: In the above template and for the purpose of this example I assumed $40 million investment, $2 million salvage value, tax rate of 38% , a CCA rate of 15% and a discount rate of 8%.

a) This is the total of the one time/upfront investments that are required for the capital project. Remember to show this as a negative figure to ensure that the total sum of all the NPV components is correct. 

b) This is the tax shield that we get from the acquired capital. Use the correct CCA rate depending on the asset. If the investment consists of several assets, each with a different CCA rate, than you will need to calculate the tax shield for each asset separately. 

c) Here we calculate the present value of the salvage. We know that salvage value in 5 years (remember deemed disposition) is $2 million but for NPV we need to calculate the present value of this $2 million. 

d) There are two components to this part. 

• First, we calculate the tax shield that we will loose due to the deemed disposition in year 5. In 'b' we calculated the total tax shield for the investment assuming no disposition due to the nature of the CCA tax shield formula (it calculates tax shield for the life of the asset). However, since NPV assumes disposition in year 5, you have to take into account the tax shield that is being lost due to the deemed disposition of the capital. (ie. if we bought a machine that would yield a tax shield of $10 over 8 years, and then we decide to sell it in year 5, we will not get all the tax shield from it as we sold it before we had a chance to benefit from all the tax shield). 
• The tax shield lost calculated so far is a figure as at year 5 (its a Future Value for us) and therefore we now need to discount it and calculate its Present Value. The second component is where we take the FV of the tax shield lost and calculate the corresponding PV. 

e) This is the Present Value of the incremental cash flows. Remember to use the after tax figure. 

f) This is the sum of all the components that shows the NPV for the investment. Remember that an NPV of $0 is good - this means that the desired return rate has been achieved. 

~This template is now available for download in excel format so that you can see all the links and formulas: NPV Template ~