NPV Spreadsheet

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
Does anyone have a NPV spreadsheet?

I just need a basic one that calculates NPV in a formula.

Any ideas?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Thanks Nate... That is what I needed but I'm having problems. What if I want to add on an Interest rate that goes with the Discount you rate.

I'd sum the PV after factoring the Interest Rate to get the NPV. How would I use Excel's NPV function to check if this is correct?
 
Upvote 0
Hello again,

Your weighted average cost of capital is your discount rate. By interest rate you imply the cost of debt, your cost of equity should be factored into your cost of equity as well. Your equity holders bear more risk and in a world where risk = return, they require a larger return rate to justify taking their position.

In my example, I use 10% as the WACC/D.R., but this will vary on a case-specific basis.

Is this what you're asking?
 
Upvote 0
To say Nate's idea differently, you should be using EBITDA for your cash flows. Your WACC is where interest (i.e. cost of debt), taxes, and required return on equity is incorporated.

i.e. WACC = debt/((cost of debt)*(1-tax)) + equity/(cost of equity)

(cost of debt = interest
cost of equity = required return for shareholders)
 
Upvote 0
Hello,
Oaktree said:
To say Nate's idea differently, you should be using EBITDA for your cash flows.
{snip}
With all due respect, that's not what I'm saying. You should not be using accruals in a NPV calculation, you should be using Cash Flows. Accruals can be very misleading with respect to cash receipts. E.g., how does the accrual P&L recognize revenue (very different amongst industries).

Accrual earnings before interest and taxes is not a good measure for cash return on investment, and the cash flows related to depreciation and amortization need to be accounted for.

Accruals have their value in the world of Finance, a NPV calculation is not one of those valued areas; you need to get back to cash.

My advice, never buy an accrual with perfectly good cash; buy cash with cash.
 
Upvote 0
In part of my spreadsheet for share purchase price, I have a formula that discounts future values back to a present value. However, I don't use the NPV function.

For what its worth I use
Code:
 P/V=F/V/(1+Discount rate)^No of years

Is this what you are after? If so I can expand a bit on how I come up with F/V.


Pedro
 
Upvote 0
Note,

F.V./(1+Discount rate)^Period

Is what I'm using for the 'proof' in my first link. This should tie back to the NPV() function if it's all implemented properly. :)

Calculating Future Values can range from straightforward in cases (like preferred shares if you assume hold-until-call) to complex and requiring assumptions with ordinary shares. Predicting the future is not easy...
 
Upvote 0
The Excel NPV function is rather limited as it assumes end of period cashflows

I prefer XNPV which can be enabled via Tools|Addins|Analysis ToolPak as XNPV allows you to enter dates against your cashflows. So it handles a model where cashflow arrives mid period or at irregular intervals.

Alternatively, compute the NPV manually year by year by working out a discount factor for each period as per the PV calculation earlier in this thread

You should not use NPV with EBITDA as EBITDA is an acounting measure that doesn't include capital or the timing of working capital assumptions. As NateO states, NPV is a cash measure.

Cheers

Dave
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top