~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 ~
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 ~
For incremental cash inflow, should we add back allocated head office costs?
ReplyDeleteAllocated head office costs that relate to expenses that do not change due to the investment (ie. head office), should have no effect on cash flows for NPV. Only items that create an incremental cash in flow or out flow should be part of the cash flows for NPV.
ReplyDeleteHi Case Master,
ReplyDeleteThank you very much for the NPV template. I used this method and it really speeded up my calculation.
The question above is from RomaCorral Foods Ltd case. The reason I add back head office allocation when calculating beofe tax incremental cashflow is because I think head office allocation cost is irrelevant for dicision making. It would stay the same whether new restaurants open or not. But I subtracted incremental head office costs when calculating net profit for 2012 and 2013.
I will have the case exam this May and am really worried because I have nobody to ask when I have questions. I also have problem to decide which option should be recommended when all option's NPV are positive. Could you please give me some advise?
Once again, thank you very much.
A headoffice allocation or a change in how a head office cost is allocated (assuming total cost stays the same) is irrelevant to NPV. NPV only considers incremental inflows/outflows.
DeleteFeel free to post all of your questions in any of the comment sections and I will gladly reply. I know how you feel - that is why I started this blog; so that people can have a place to ask questions.
When all options have a positive NPV, I recommend choosing the alternative which is the most appealing as a whole (cosnider the qualitative aspects as well as which of the alternatives has the biggest NPV).
Remember, there is no single 'right' alternative. It is how you present your case and recommendations.
Thanks for your blog, its fantastic. Can you tell me how FV of CCA lost on Salvage Value (477,295) is calculated in part D? Thanks again!
ReplyDeleteLol, sorry nevermind. Guess it was time to quit studying for the night when I wrote this, which I did!
ReplyDeleteno worries, we all have those days :)
ReplyDeleteMaybe I missed something but can you explain why n = 15?
ReplyDeleteHi, that is my mistake, i posted the template for a calculation i did for a 15 year npv not 5 year. I will fix and re-post. it should be 5 years for the purpose of the case exam. thanks for catching this!
Deleteok fixed, n is now 5 - thanks again for pointing this out!
DeleteGreat! I was confused and thought I was missing something important! Thanks for all the info on here - very helpful!!
DeleteHi Case Master,
ReplyDeleteFor the case Celebrations and Memories , I do not understand why the solution from CMA Canada to markers on page 62 is taking the salvage value of the building after 5 years - the case does not state the building is for sale nor what the amount is. Would you know why? I can only think that the equipment used has 5 years life therefore they are selling at 1,000,000 discounted for N= 5 years at 9 per cent interest.
that is because when you calculate a 5 year NPV you apple the concept of 'deemed disposition' - what this means is that you assume that the capital acquisitions will be disposed of after 5 years (this has nothing to do with whether or not the business plans to actually dispose of these investments not does this have anything to do with useful life of the item, this is done for the purpose of a correct 5 year NPV calculation).
DeleteHi Stephen,
DeleteWhere can you find the solution for Celebration and Memories? I copied part of solution from the interactive session, but don't understand why the after tax gross profit for Collection Plates become 1111, 875, 874, 874, 873 for 2012 to 2015.
Thanks.
If you are talking about salvage value of the warehouse, the salvage value is $1000000 because its value is not expected to decline over the next 40 years (p4 additional information). If the case didn't state this, should we just calculate salvage value as 1million/40 *35?
DeleteI also have a question about lease for this warehouse option. If I decide to lease, should I subtract after-tax lease expense when calculating after tax cash flow? what about the interest expenses?
Thanks
I think the salvage value assumption seems reasonable if no other info is available.
Deleteif you are going to incur incremental lease costs in an alternative (not referencing a specific case, i am referring to any potential alternative) then you should account for these in the cash flows calculation.
financing costs should not be part of the NPV calculation.
Hello,
ReplyDeleteFor the PV of Total Tax Shield Lost From Salvage, I use formula c to be provided to us on the formula sheet. When I do use that I come up with $156,250. Would it be ok to go straight to that formula c for every case? I did the numbers within Celebrations and Memories on page 62/63 using 5 years as n and I was right on to their numbers using the formulas from the formula sheet.
Although they say "CCA tax shield lost from salvage of building in 15 years" which I think they meant 5 years.
Thanks.
i am not sure what you are asking... are you wondering if you can apply the tax shield formula for every case? if so, i do not see why not. You said that your calculations match those of the marking key - sounds like you are doing something right.
DeleteHello,
DeleteSorry about that. I should have directed you to the part of your calculation that is confusing me a little. My direct question is: why is the formula PV of Total Tax Shield Lost From Salvage (formula c on the given formula sheet) not used in place within your step d? Our moderators always just directed us to the formula c on the sheet. When I used that formula I was out by $6,000 from your way, so then I got concerned that the moderators may have not mentioned something. However, I have never seen the calculation that is done within your step in any practice exams. If you could provide info as to why the formula c is not just simply used that would be great.
Thanks so much and thanks for all your hard work answering the questions!
Hi,
DeleteI am not sure which forumula exactly you are referring to as I do not have the formula sheet but my guess is that being off by only $6,000 (when the calculation is on a $2 million salvage value) is attributable to rounding which can result from using different formulas/calculation methods. When I calculate NPV my goal is not to get it perfect, but to get it as accurate as possible within as little time as possible - I am trying ot maximize marks per minute.
If you post the formula here, I can confirm if it is in fact rounding or not.
The reason I use the above approach is because I find that the above template is fairly universal and can be easily and quickly applied to most strategic alternatives. I would just type it out once and then copy and paste the template for each of the strategic alternatives. I would then just fill out the amounts, rates and years and the NPV would calculate itself, therefore eliminating me having to recalculate NPV again and again for each alternative (it also makes sensitivity analysis a breeze).
Also, with the above template, if I needed to make a change to an assumption I made, I could just change one figure and the NPV would recalculate itself right away.
My rule for the case exam is to avoid the calculator as much as possible and to use the formula functionality of securexam as muc has possibel to let it do some of the work for me.
Hello Case master, I want to practice old exams using securexam. Do you know how to open the .ssi file it creates? If there's no way to view it, how to mark by ourselves? Thank you, your website is really helpful!
DeleteThanks CM for the tip above, I wasn't successful at imputing the formula's in excel but at least I can work with the format.
ReplyDeleteWith regards to calculating NPV for NFP's how do you treat the tax component since they do not pay taxes? Example cash inflows usually is after tax $$ do we still use after tax or before tax (assuming they provide a tax rate to throw us off).
Thanks again.
If a company does not have to pay taxes, then you should not use after tax cash flows - use info that is relevant to the company.
DeleteThis comment has been removed by the author.
DeleteThanks a bunch... I realised my error soon after posting. I wasn't using double brackets (( )) I was using these [( )]
DeleteHi there,
ReplyDeleteJust wondering your thoughts on how to best incorporate the incremental annual cash flows into your template if the amounts will be different each year, such as in the Ferndale Public Library case? Thanks!
you can alter the template to reflect the different annual cash flows. Personally though, if I were tight on time, and the annual cash flows were different but not by too much, I would probably make some simplifying assumption and use an average number to make the calculation easier. This would probably cost some marks but to me this is all about maximizing marks per minute.
DeleteI sometimes find it difficult to determine whether an NPV analysis is appropriate, or a Cost Revenue flow (showing incremental revenue year by year) is appropriate. Any points that may provide me with a deciding factor? THanks.
ReplyDeleteFor major strategic alternatives that include a capital investment I always calculate an NPV. If it is a minor alternative with no capital investment, then depending on the info, showing revenue vs cost analysis may be ok.
DeleteHi Case Master,
DeleteI get confused by how to calculate after tax cashflow. Sometimes it equals to net income + depreciation, some time it equals (income before tax +depreciation)(1-tax rate). I think the first formula is correct, but our moderator had a NPV calculation example for Aqua Fish used the second approch. Could you please help me with this? I found I have trouble to determine a good format to test constraints such as times interests earned, debt to equity and earning per share. Do you have some suggestions?
Thanks
hello, i didn't worry too much about the 'ideal' way of calculating it. If CMA is using various approaches, I would just pick one that makes sense to me and use that one.
DeleteOur moderators told us not to worry about getting the quants 'perfect' if it means that we need to spend a lot of time on it. So my appraoch was always to not worry about doing it exactly right, rather to do it good enough so that it is reasonable and appropriate and then move on to the other sections.
I do not have any templates for the other calculations but I didn't aim for perfection with these either. My goal is 'reasonable'.
Hi Casemaster,
DeleteCan you help in determining what will the N for CML case for Expansion alternative?? We have to calculate incremental cash flow favor that N is required but case does not say anything about N
Thanks
May9
I do not have the details for this specific case, however my approach to other cases was as follows:
DeleteUnless otherwise specified, I generally assumed that n=5 (unless otherwise noted I calculated NPV for 5 years)
If I am doing a profitability analysis that does not relate to any NPV, then I will do for at least 3 years (ideally if I have enough time i aim for 5 years).
Thanks for your reply..
ReplyDeleteMy problem is sometimes I struggle with finding strenghts with AI.. As in AI is full of weaknesses and I can hardly find 2-4 new strenghts from AI... what would you recommed here???
Also no matter how hard I try I cant prepare proforma statement and if I aim at preparing proforma in any case then I cant do quantative analysis for all three (generally 3) alternatives... So what would you recommend here... and what should be the strategy???
Thanks
May9
I think the best way to learn to identify strengths is to practice and review past exams.
DeleteWhen I wrote the exam I also did not have time to do the full pro forma so what I did was a VERY simplified version of pro forma, where I showed only the key lines for that case (ie. for income statement i calculated revenues, expenses and net income). It is not ideal but that was the best that I managed to do with the time constraints. I did calculate NPV for all of the strategic alternatives - I believe that this is important.
Why did you include the half year rule in the tax shield lost from salvage for d?
ReplyDeleteThe formula for TS from salvage is (S/(1+k)^n)*(dT/(d+k)) which does not include the half year rule, which only applies when the asset is purchased.
Hi Jean, you are right, i had a formula error in the template - I am fixing and will re-post. Thank you!
Deletedone - the above template has been changed to reflect the salvage value tax shield formula. the template calculates the salvage value tax shield first and then the present value.
DeleteHi Case Master,
DeleteA Few questions:
1. When provided incremental cash flows for a project which specifically include depreciation and intenrest expense from a line of credit, these two costs are added back to determine true incremental cash flows, correct?
2. If there are restoration costs required in year 2 on a 5 year project, these costs are a one time outflow in year 2, correct?
Thanks,
1. yes, I would add these back. depreciation is not a real expense and interest is not part of NPV calculations.
Delete2. yes
good luck!
The image link to this template no longer works. Can you reupload? Thanks!
ReplyDeleteIt looks fine on my end. Maybe its something on your end? If you cannot make it work, i can email it to you.
ReplyDeleteWorks now. Thanks!
ReplyDeleteHi, CaseMaster,
ReplyDeleteAt the end of the NPV template, there are 2 formulas provided in the box, Tax shield on salvage value and Tax shield on new asset. In the Tax shield on salvage vaule formula box, it seems like the C should've been b - salvage value instead of C- investment.
Thanks.
Thanks (i think you meant S not b), I will add the definition of S to the legend.
DeleteJust to point out that the formulas and template are correct, it is just that in the legend where I explain what each letter stands for, I included the definition of the letter C twice, and did not include the definition of what the letter S stands for. In the meantime before I update the legend, the lettter S stands for Salvage Value.
Hi CM,
ReplyDeleteThe NPV template is in PDF format. Any link of it in excel?
Thanks.
I chose not to post the excel so that students can learn to create this themselves... let me know if you are having a hard time with re creating any of the components in excel and i will be glad to help.
DeleteHi - I can easily calculate NPV using the calculator but have not yet mastered excel formulas. Can you please provide additional information on how to create the NPV formula?
DeleteThanks!
i did a post about PV/FV/PMT formular for excel, check it out:
Deletehttp://cmacasemaster.blogspot.ca/search/label/Excel%20Tips%20and%20Templates
the other formulas are identified above.
if there are any additional formulas you want to see, please let me knwo which.
CM
Hi CM,
ReplyDeleteIn part e, was the 7,169,036 annual incremental cashflow a part of the question? If not, how was this number calculated?
Thanks so much! :)
the incremental cash flows are either provided or you have to derive it given case facts.
DeleteHello Case naster,
ReplyDeleteThanks for taking time to answer questions.
Please how do i factor growth rate into incremental cashflow. i.e if cashflow is expected to grow at say 5% per annum for 15 years and 0% into perpertuity.
Thanks in advance.
Hi,
DeleteSince NPV is generally calculated for 5 years, i would not include cash flows into perpetuity... just cash flows for 5 years. To calculate 5 years of cash flows with 5% growth I would do a quick table with cash flows growing by 5% annually and then bring each of these annual cash flows back to PV for a total PV of cash flows. Dont forget to do incremental only, and after tax.
Do you have the answers for Celebrations and Memories Ltd Case?
ReplyDeleteThanks,
I do not post answers to CMA cases (whether I have them or not).
DeleteI am doing fleet management group Inc. I am doing the quantitative analysis for the police alternative. I am not sure how to calculate the interest expense. Can you show me how to calculate interest expense for all years. Thanks,
ReplyDeleteI do not have details of the case, however I also do not help with specific cases from CMA... If you have questions regarding overall approaches, strategy, etc. please feel free to post.
DeleteHello Case master!
ReplyDeleteWhy do we enter -2,000,000 for salvage value. And not Positive 2Million. Isnt the idea that salvage value is a cash inflow and therfore should be treated as a positive value. But then the answer comes negative. I know this is probably the dumbest question but I thought I'd ask away! thanks for your help!!!
I believe this has more to do with the calculator inputs than the NPV analysis. If you look to the right you will notice that number plugged into the NPV calculation (the PV of the salvaged equipment) is positive.
ReplyDeleteHi Case Master!
ReplyDeleteI appreciate the way you presented the NPV template. I am just wondering why the amount of salvage value in "c) PV of Salvage value" is in negative. Is it not supposed to be positive cash flow since the company will received cash from the proceeds of the sale of the asset if sold at salvage value? I notice that the PV is positive amount but why the input on FV of($2,000,000)is in parenthesis? Does the negative sign has some implication on the computation? Thank you very much!
Hi Case Master!
ReplyDeleteI'm sorry for being redundant on the question regarding the negative sign in $2,000,000 in step c:. When I read the whole blog before this I realized you already answered the same question. Kindly accept my apology.
Hi,
ReplyDeleteI was little knocked off while calculating gross margin for Acqa Fish. I was confused about which costs could be inventoried? Can you please provide some guidance for that?
try reaching out to other candidates on the case master FB page. I do not have the details of this case and definitely do not remember from before so can't answer this.
DeleteHi, Thanks for the template. I am just wondering how did you obtain PV of incremental cashflows of $7.17M?
ReplyDeleteNo where on the site mentioned about the cashflow, maybe I am missing some info?
Thanks.
the 7.1MM is just a number for the purposes of demonstrating the way I go about using the template.
ReplyDeleteHi Case Master,
ReplyDeleteCan you tell me why the cash flows from the $2M salvage value don't get taxed like the rest of the incremental cash flows?
Thanks!
Hi Case Master - in Year 2, it's up to us to figure out how long the discount period is and we got in trouble for assuming that cash flows would go on for 20 years - apparently it is too long. I know that it depends on the project, but how do we find out?? What should we actually research on to figure out the life of a project?
ReplyDeleteIn my experience, unless otherwise suggested or indicated, we would do a 5 year NPV with deemed disposition at year 5.
DeleteIs it possible for you to send me the CMA solution to the Case "Celebration and Memories? My email is ttccricket@yahoo.com - Thanks Peter
DeleteHi, i do not provide solutions to any CMA cases.
DeleteHello CM,
ReplyDeleteThank you for sharing your template. I have two questions.
1) When calculating NPV, what do you do if you are not given a CCA rate, but told that the capital asset uses straight line depreciation? For example, furniture costing $700 with a five year useful life.
2) When calculating NPV of a project, if you have a constraint of having to attain an after-tax return of 10% within 5 years, how would you determine this? Is it simply dividing your NPV by the initial investment?
Thank you kindly.