# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  How do I set up an ROI calculator in Excel?

## katem70

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

----------


## Leith Ross

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

----------


## Bill Martin

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

----------

