How do I set up an ROI calculator in Excel?

Does anyone have any input on putting together a return on investment
calucation in excel?

Hello katem70,

You didn't say which ROI equation you wanted to use. Here are the 5 most common equations used. Excel has builtin functions for NPV and PV calculations.

ROI Equations:

1. Standard Equation

ROI = Net Benefits/Cost * 100%

Net Benefits = Benefits – Costs
Costs = Initial and Recurring/Ongoing Project Costs
Time Period = The standard ROI equation is usually calculated for the first year of the investment.
_______________________________

2. Return on Investment Captial

ROI = NOPAT/Invested Capital * 100%

NOPAT = Net Operating Profit after Taxes
Invested Capital = Same as costs above (initial and recurring project costs)
_______________________________

3. Return on Investment using Net Present Capital

ROI = NPV (Benefits)/PV (Costs) * 100%

NPV of Net Benefits = Present Value of Benefits – Present Value of Costs
PV (Costs) = Present Value of Costs
This equation accounts for the time value of money or the “interest” derived from investing in an investment with similar risk. The Present Value is discounted at the cost of capital of the company or the “rate” the company could borrow money in the marketplace given its risk level.
_______________________________

4. Financial Definition

ROI = Profits/Firm Assests * 100%

Profits = The ROI financial definition is used to estimate the return of a company’s profits or net income. Net income in turn is defined as the gross income less deductions for depreciation, interest payments, and taxes.
Firm Assets = Company resources including cash, accounts receivable, inventory and properties
_______________________________

5. Best View

ROI = Total Net Benefits/Costs * 100%

A typical use of this equation would be for a software vendor whose do not include recurring or operating costs.

Net Benefits = Benefits – Initial Costs
Costs = Initial Costs

If you are familiar with Excels worksheet functions it won't be difficult to compute your ROI. You just need to decide which of the equations best represents your needs,

Sincerely,
Leith Ross

katem70 wrote:
> Does anyone have any input on putting together a return on investment
> calucation in excel?
---------------------------------

Ask Excel's Help system about the XIRR() function.

Bill