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:

**NPV = Cash Flow / rate**

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:

**NPV = $120K / 12% = Cash Flow / rate = $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).

***************************************************

**Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss**

***************************************************

## 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

## Muzah

Given that the expected return on equity is 15%, the expected return on bonds is 10%, the variance of equity is 400, the variance of bonds is 200. the covariance of bonds and equity is 100. the risk free rate is 6%. calculate

1. weight of debt

2. weight of equity

3. the expected return of the portfolio

4. the variance and standard deviation of the portfolio

5. reward to variability ratio

6. the position an investor with a coefficient of risk aversion A=3 would take in the risky portfolio

## directvelo.com

The tail or flagellum has a vital role in fertilization.

Due_to_progressive_deterioration_of_muscle,

_loss_of_movement_occurs_eventually_leading_to_paralysis.

Urinary System: This system is primarily responsible for getting rid of excess wastes in the body and regulating body fluids.

## Please Leave a Comment!