My philosophy on maintaining this blog site is very different from that of most bloggers. Rather than update it constantly with the latest happenings, I aim to write longer how-to explanations and tutorials that are less time-sensitive and provide useful reference material long after they’re written.
Though my posting rate has declined dramatically recently (this will improve soon), I still receive questions from readers, mainly about using Excel functions. Last week, I received one from a reader named Jae, who asked a Finance 101 question:
I have a problem in my finance class where the annual revenues from a project is $500,000 and annual costs are $300,000. The corporate tax rate is 40% and the cost of capital is 12%. How do I calculate NPV of the project?
The original problem has probably been paraphrased, and lacking other information, here’s how I’d interpret the problem. The project generates pre-tax revenues of $500K a year, and incurs operating costs of $300K a year. The corporate tax rate is 40%, the cost of capital is 12%, but nothing is provided about how long the project will last. For simplicity’s sake, let’s suppose it lasts 5 years and then ends at the end of year 5.
The setup for solving this problem is very similar to another post I wrote on how to calculate net present value (NPV) a while back.
Here’s how I’d set up the calculation in Excel to solve this problem, assuming the project lasted 5 years:
Cash flows are defined as revenues less costs (in this case, $500K per year less $300K per year, or $200K net per year). But we also have to take taxes into consideration, because they’re also a “cost”. Since the tax rate is 40%, we need to deduct an additional $80K per year, which we calculate by taking $200K per year * 40%.
Subtracting the annual costs and taxes from the revenue leaves us with $120K per year. We then use Excel’s NPV function and fill in the cost of capital and annual cash flows, and Excel does the rest:
The NPV for this 5-year project is $432,570.
Another way to approach this problem would be to assume that it’s a project that lasts forever, since there’s no time period given. In this case, we’d use a perpetuity formula:
Annual cash flows are still $120K, calculated identically as we did above, and our rate is our cost of capital of 12%. In this case, the NPV of the project would be a round $1M:
Without more information on the timeline suggested in the problem, we might assume this second solution is what the professor is looking for.
If you’d like to better understand the principle of DCF, which underlies NPV calculations, check out the post I wrote on how to calculate discounted cash flow (DCF).
Popularity: 4% [?]
frank
Welcome back! You’ve been missed in the blogosphere.
rqueen2200
I expexct to save 500,000 per year for 10years. My cost capital is 14%.
I believe it can purchase a new supplier for 2 million. How do I calucale NPV,IRR, and payback
Vernon Steen
Thanks for breaking down NPV, DCF, IRR into simple terms. It’s still difficult for me (a non-finance guy) to grasp, but you make it easier.
Sylvia
Here is a problem I need to figure out:
$1 million in net working capital to start
Additional NWC each year equal to 35% of projected sales increase for following year, no NWC for 5th yr
Total fixed costs of $175,000 per year
Variable costs of $227 per unit, units priced at $360 each
Needed equipment to begin $13.2 million
Equipment considered industrial machinery falls into class 8 (20%) for tax purposes
$6 million of equipment eligible for a 5% ITC
In 5 yrs equipment sold for scrap value of $1million
Government offering non –taxable grant of $500,000 payable a year after start of project
Company is in 40% tax bracket
ROR is 25%
Year Unit sales
1 90,000
2 100,000
3 110,000
4 117,000
5 65,000
How do I calculate NPV and IRR and is it a worthwhile project?
Isaac mangwazu
What do u do with the scrap value givn in NPV. for example, a company plan’s to buy equipment @ a cost of 100,000 & the net cash flows from year 1 to 5 are 25000 & scrap value is 10000. Analyse whether the equipment is worth purchasing. Plz send answer @ http://www.Zexbwoy@gmail.Com
Jaime Adriance
How do you calculat the NPV on a $10,00 loan at 15% interest when the conditions are the interest is to be repaid annually for 5 years and the non-amortized principal is due at the end of the fifth year. The tax rate is 34%
Eva Hoekzema
I came upon your website today. In fact it helped to understand a NPV problem I needed to work. I need help with another problem. How can I secure your help/feedback.
Mike
How do you use excel to compute imputed interest?
Problem:
6,000,000 loan
8% @ yr. compounded semiannually
Payment 300,000 at the end of each year
olu
pls, i have an asignment here on NPV. but the figures given are profil for each year not cash flow. i was also given a scrap value of “`£40,000 at the end of the 4 years. with 20% cost of capital.. how do i calculate NPV.
thanks
chef Geno Bahena
Please help me with this problem
I want to know how to calculate my percentage of food cost
1Lb of Shrimp u 12 cost me $10.00
Im serving 8 oz. portions 6 shrimp %5.00
sauce .50 cents 4oz.
accompaniment .50 cents 4oz.
adds a dollar more to my portions now the portion cost $6.00 Dollars
Cost $6.00
Labor $6.00
Profit $6.00
X 3 this is the price I shoot have in the menu. I’m I correct?
Food Cost%. ? what’s my food cost%
my cost, cost pluss labor $12.00
my Profit, Profit $6.00
if you may have any question in helping me with this do not hesitate to call: 505 5772130 im going crazy
thaks a lot Geno Bahena
Conrad
I have used your sample formula for successfully calculating the NPV on a cash flow scenario over a five year horizon. whilst the net annual cash flows differ each year, how do I calculate the NPV in perpetuity assuming I accept a position in perpetuity of the same net income stream as year 5. Is there an excel formula that I can adopt / use for calculating this.
Many thanks / p.s your user notes are an excellent guide.
Conrad.
Finance student
Kolby’s Korndogs is looking at a new sausage system with an installed cost of $631,800. This cost will be depreciated straight-line to zero over the project’s 7-year life, at the end of which the sausage system can be scrapped for $97,200. The sausage system will save the firm $194,400 per year in pretax operating costs, and the system requires an initial investment in net working capital of $45,360. If the tax rate is 31 percent and the discount rate is 16 percent, the NPV of this project is $
Thomas Nashixwa
Dibble Ltd. has three capital investments projects which they wish to evaluate. They are A, B and C. Basic Information are as follows
A B C
Total Sales 22.90 22.80 25
Profit Before Tax 10.70 9 13
Depreciation Charg. 1.75 1.35 2.05
investment 38 2 2.50
Residual Value 2.40 2 2.50
Conditions: weighted average cost of capital: 12%, funding required from internal sources. Only one project can go ahead. Project is for 5 year. Depreciation Rate is 10% .Need Payback, NPV and IRR
Madu
hi! followed your calculation when working out the NPV but excel comes up with a 508 error. not sure what im doing wrong. any help will be appropriated. thank you
Noel Frias
I am having a little trouble solving my assignment on financial management. Here is the question: A project has an initial cost of $52,125, expected net cash inflows of $12,000 per year for 8 years, and a cost of capital of 12%. what is the project’s NPV? What is the project’s discounted payback period?
Please help.
mohamad
Dog Up! Franks is looking at a new sausage system with an installed cost of $800,000. This cost will be depreciated straight-line to zero over the project’s 4-year life, at the end of which the sausage system can be sold for $85,000. The sausage system will generate for the firm of $ 175,000 for the first year, and sales will grow at 10% per year thereafter. Cost will be $30,000 per year. The system requires an initial investment in net working capital of $70,000. The tax rate is 37 percent.
In excel, calculate the NPV of the project at discount rates of 8, 14 and 16 percent. Graph the NPV profile using excels. Explain how risk is included in the capital budgeting process, and explain whether you should take the project or not.
vicky
the macinnery will cost $2.5 mllion,payable at the start of the yer of operation, and is not expected to have any scrap value.annual befor tax net cash flows of$680,000 per yea would be genrated by the investment in each of the five year of it’s expexted operating life.these netcash inflows are befre taking account of expected each oh the five yers of its expected operating life.these net cash inflows are befoking account of expexted inflation of 3% per yea. initial investment of $240000 in working capital would als be required, fowed by incremental annual investment the purchasing power of working capital. rupa co has in issue five million shares with a market value £3.81 per .the equity beta of the company is 1.2 . the yield on shor-term goverment debt is 4.5% per year and the equity risk premium is approximantely 5%r year. the debt finance of rupab co consists of bounds with a total book value of $2 million. These bonds pay annual interest before tax of 7%.the par value and market value of each bond is $100.Rupb co pays taxation one yer in arrears at an annual rate of 25% capital allowances(tax-allowable depereciation) on machinaery are on a straight-line bass over the life of the asset. prepar a fore of the annual after-tax cash flows of the investmet in nomnal terms, and calculated and comment on its net present value. how can i do this sum plz reply as soon as possibal
momi
Can you please help me solve this problem:
A firm is considering the cost-saving project of replacing an existing copier with a new copier.The existing copier was purchased 8 years ago for $ 100,000 and was expected to last 20 years over which straight-line amortization was used. This existing copier can now be sold for its book value.The new machine costs $120,000,will last 12 years,and is expected to save the firm $20,000 each year before taxes. Assuming a tax rate of 34%, a discount rate of 10% and a zero salvage value for the new machine at the end of its life,what is the NPV for this project?
Thanks
judy quamina
chill plc is an established manufacture and retailer of large fridge and freezer equipment for commerical outlets such as shops and resturants. They supply equipment across the midlands and south of england.
Before final approval is granted for a new product, a full financial analysis is under taken for presentation to the board.
A new product , RetroCold, is about to be evaluated and the following projections have been prepared:
The equipment will be leased to outleta for a fixed term of five years at $1300 per year . 54 current outlets have already registered their interest in the product. At the end of the five years, the outlet will have the opporunity to purchase the equipment from a charge $875. It is expected that 36 of the outlets will take up the offer. The remaining equipment will be donated to shelters for the homeless.
the annual lease covers maintenance and service charge. Each machine will be serviced three times a year by chill engineers. Each visit to an outlet takes approximately twohours and cost chill $140. In addition, in th case of a fault developing, and outlet can call out an engineer. There are approxmately 40 call outs a year, which is expected to be the same for the new product and each call out cost $95.
To manufacture RetroCold a new piece of machinery will be required wich will cost $35,000. This will a five year life and will have a scrap vaule of $1,000.
The variable cost of producing each RetroCold on this machine are Material $225 Labour $165.
In addition ther will be fixed cost specific to the product will be charged with 25% of direct cost to cover general fixed overheads of the business.
The company cost of capital is 12%
(a) Calculate and explain the meaning of the net present value of teh proposal using the current cost of capital.
Please Leave a Comment!