Simple Savings Calculator

Simple Life-Cycle Energy Savings Calculator Client Project Alternative Description Inputs Study Period (Years) M&O Inflation Rate Electricity Inflation Rate Fuel Inflation Rate Hurdle (Re-invest) Rate Discount Rate Initial Incremental Cost Annual M&O Cost Annual Electricity Savings Annual Fuel Savings $ $ $ $ 7 3.0% 3.0% 4.0% 15.0% 10.0% 55,000 1,000 17,705 3,750 Results Simple Payback Years (SPB) Simple Return on Investment (ROI) Net Present Value of Savings (NPV) $ Internal Rate of Return (IRR) Modified Internal Rate of Return (MIRR) 2.69 39.01% 56,104 36% 24% Total Annual Savings (Initial) $ Annual Cash Flow 20,455 Year in Study Period Totals Incremental Costs M&O Costs 0 $ (55,000) 1 $ (1,030) 2 $ (1,061) 3 $ (1,093) 4 $ (1,126) 5 $ (1,159) 6 ($1,194.05) $ (1,194) 7 $ (1,230) 8 N/A 9 N/A 10 N/A 11 N/A 12 N/A 13 N/A 14 N/A 15 N/A 16 N/A 17 N/A 18 N/A 19 N/A 20 N/A 21 N/A 22 N/A 23 N/A 24 N/A 25 N/A 26 N/A 27 N/A 28 N/A 29 N/A 30 N/A $ (56,194) $ (7,892) Electricity Savings $ $ $ $ $ $ $ N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A $ 18,236 18,783 19,347 19,927 20,525 21,141 21,775 Fuel Savings $ $ $ $ $ $ $ N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A $ 3,900 4,056 4,218 4,387 4,562 4,745 4,935 139,733 30,803 Total by Year $ (55,000) $ 21,106 $ 21,778 $ 22,472 $ 23,188 $ 23,928 $ 23,497 $ 25,480 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A $ 106,450 Present Value in Year 0 $ (55,000) $ 19,187 $ 17,999 $ 16,884 $ 15,838 $ 14,857 $ 13,264 $ 13,075 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A $ 56,104 User Guide Table of Contents Client Project Alternative Description Inputs Study Period (Years) M&O Inflation Rate Electricity Inflation Rate Fuel Inflation Rate Hurdle (Re-invest) Rate Discount Rate Initial Incremental Cost Annual M&O Cost Annual Electricity Savings Annual Fuel Savings Results Simple Payback Years (SPB) Simple Return on Investment (ROI) Net Present Value of Savings (NPV) Internal Rate of Return (IRR) Modified Internal Rate of Return (MIRR) Total Annual Savings (Initial) Special Calculations Future Incremental Costs Annual Cash Flow Table Year in Study Period Incremental Costs M&O Costs Electricity Savings Fuel Savings Total by Year Present Value in Year 0 TOC Return User Guide Terms (In Alphabetic Order) Alternative Description Briefly describe the project alternative being analyzed in this spreadsheet, so as to differentiate it from other alternatives. Annual Electricity Savings (Inputs) Insert the current year dollar value of estimated annual electricity savings for this alternative. Annual Fuel Savings (Inputs) Insert the current year dollar value of estimated annual fuel savings for this alternative. Annual M&O Cost (Inputs) Insert the current year dollar value of estimated annual maintenance and operations costs. If you believe that M&O costs do not vary between project alternatives, you may enter zero for this value in all project worksheets. Client Insert the name or other brief description of the project owner. Discount Rate (Inputs) The rate of interest, reflecting the investor's Time Value of Money (or opportunity cost), that is used in Discount Formulas or to select Discount Factors which in turn are used to convert ("discount") Cash Flows to a common time. Unless you are specifically required to use a Real Discount Rate, use a Nominal Discount Rate in this spreadsheet. Real Discount Rate Real Discount Rates reflect Time Value of Money apart from changes in the purchasing power of the dollar (inflation) and are used to discount Constant Dollar Cash Flows. They do not include an adjustment for inflation, since constant dollars are are used to discount Constant Dollar Cash Flows. They do not include an adjustment for inflation, since constant dollars are used. However, when real discount rates are used, if specific items are being considered that vary from the base rate of inflation, adjustments must be made to their inflation rate. As an example of real discounting, if fuel prices are inflating at 2% more than the consumer price index rate (base rate), then fuel prices would need to be inflated only by the 2% not by sum of the CPI inflation rate and the 2%. Nominal Discount Rate The rate of interest (market interest rate) reflecting the time value of money stemming from both changes in the purchasing power of the dollar (inflation) and the real earning power of money over time. As an example of nominal discounting, if fuel prices are inflating at 2% more than the consumer price index rate (base rate), than fuel prices would need to be inflated by the sum of the CPI inflation rate and the 2% not just the fuel inflation excess rate of 2%. Calculating the Discount Rate A simple method that works well for use in this spreadsheet is to consider the marginal pretax rate of return on an average investment in the private sector in recent years (7%) as the Real Discount Rate and then add to it the current base rate of inflation (for this spreadsheet equal to the "M&O Inflation Rate") to create the Nominal Discount Rate. At the present time this would yield a nominal discount rate of approximately 10-11%. If the marginal pre-tax rate of return on an average investment for your organization is known, it can be substituted for the 7% number in the simple calculation above. (Note, this is different then the "Hurdle Rate" since it is the average of all investment results, instead of the anticipated rate of return on investments.) Electricity Inflation Rate (Inputs) Insert your current estimate of the annual electricity inflation rate (percentage) for the duration of the study period. Electricity Savings (Annual Cash Flow Table) This column details the calculated electricity savings, adjusted for inflation, for each year of the study period. Fuel Inflation Rate (Inputs) Insert your current estimate of the annual fuel inflation rate (percentage) for the duration of the study period. Fuel Savings (Annual Cash Flow Table) This column details the calculated fuel savings, adjusted for inflation, for each year of the study period. Future Incremental Costs (Inputs - Annual Cash Flow Table) For Project Alternatives where the Study Period is more than 5 years, it is common that there will be major maintenance or partial equipment replacements that have significant cost. As an example, a roof-top HVAC system with a 20 year life will ordinarily require a compressor replacement/repair at 10 years which will cost about 1/3 of the price of a new complete rooftop HVAC system. These "future incremental costs" do not occur on an annual basis and, thus, can not be considered part of the annual maintenance costs. They should be entered separately, in the year they occur, in the Incremental Costs column. When entering them, they must be adjusted to the future value that they would have in the year that they occur. Determining the Future Value With regard to determining the future year value, normally one of two possible situations occurs. The first situation is that the value in the year that it will occur is already known from other sources. In this case, simply enter the correct value (as a negative number) in the appropriate Year in Study Period row in the Incremental Costs column (column C, rows 25 through 54). The second situation is that only the "Present Value" is known for the "future incremental cost" and its value in the year that it will occur must be calculated. In this situation, enter the Microsoft Excel Formula for Future Value (described below) in the appropriate Year in Study Period row in the Incremental Costs column (column C, rows 25 through 54) to calculate the appropriate value. The Microsoft Excel Formula for Future Value Syntax: =FV(rate,nper,pmt,pv,type) Hurdle (Re-Invest) Rate (Inputs) The rate of interest, reflecting the investor's Time Value of Money (or opportunity cost), that is used as a minimum qualifier for any project to be funded. For considering projects to be funded, it is usually compared to each project's Internal Rate of Return. Determining the Hurdle Rate Ordinarily, this rate is supplied by the management of the project owner organization. Usually it is higher than the Discount Rate, which ordinarily only considers the cost of capital (and inflation). The Hurdle Rate is larger than the Discount Rate in order to compensate for risk of project failure and the investor's desire for profit. For use in the Simple Savings Calculator, use the value supplied by the management of the project owner organization. If no value is available, a useful rule of thumb is 15% which is equivalent to a 5 year Simple Payback on equipment with a 10 year useful life. (Note, the Hurdle Rate is also used in the Simple Savings Calculator for the calculation of the Modified Internal Rate of Return.) Incremental Costs (Annual Cash Flow Table) This column details the incremental costs, adjusted for inflation, for each year of the study period and the initial period. If Future Incremental Cost calculations have been added, their values are shown here, as well as the Initial Incremental Cost. (See Future Incremental Costs for instructions on adding incremental costs that occur in the future.) Initial Incremental Cost (Input) Insert the estimated dollar cost of design, construction and miscellaneous soft costs for the installation of this project option. (Depending on analysis scope this may be total project cost or an incremental option cost.) Only include costs that occur prior to the start of use. Future incremental costs are considered separately (if at all) in the year they occur. Internal Rate of Return (IRR) (Output) The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. The internal rate of return (IRR) is a capital budgeting metric used by firms to decide whether they should make investments. It is an indicator of the efficiency of an investment, as opposed to net present value (NPV), which indicates value or magnitude. A project alternative is a good investment proposition if its IRR is greater than the internal rate of return that could be earned by alternate investments (investing in other projects, buying bonds, even putting the money in a bank account). Mathematically the IRR is defined as any interest rate that results in a net present value (NPV) of zero of a series of cash flows. In general, if the IRR is greater than the project alternative's hurdle rate the alternative will add value for the company. M&O Costs (Annual Cash Flow Table) This column details the calculated annual M&O Costs, adjusted for inflation, for each year of the study period. M&O Inflation Rate (Input) Insert your current estimate of the annual maintenance & operations inflation rate (percentage) for the duration of the study period. Normally, this is the general rate of inflation for the regional (USA) economy as a whole. Currently, as of 2008, it is about 3-4%. Modified Internal Rate of Return (MIRR) (Output) Modified Internal Rate of Return (MIRR) is a financial measure used to determine the attractiveness of an investment. It is generally used as part of a capital budgeting process to rank various alternative choices. As the name implies, MIRR is a modification of the financial measure Internal Rate of Return (IRR). MIRR makes an explicit assumption about the rate of modification of the financial measure Internal Rate of Return (IRR). MIRR makes an explicit assumption about the rate of return of investment of those flows. (The Internal Rate of Return assumes, by leaving a specifc rate unstated, that interim cash flows are re-invested at the IRR. If the IRR is significantly higher than the hurdle rate, this is unrealistic.) The modified internal rate of return assumes all positive cash flows are re-invested to the terminal year of the project. In the Simple Savings Calculator, this re-invest rate is equal to the hurdle rate. All negative cash flows are discounted (using the discount rate) and included in the initial investment outlay. MIRR ranks project efficiency consistent with the present worth ratio (variant of NPV/Discounted Negative Cash Flow), considered the gold standard in many finance textbooks. The Simple Savings Calculator uses the following Microsoft Excel formula and values: =MIRR(values,finance_rate,reinvest_rate) =MIRR(values,Discount Rate,Hurdle Rate) =MIRR(values,B14,B13) Net Present Value of Savings (NPV) (Output) Net present value (NPV) is a standard method for the financial appraisal of long-term projects. Used for capital budgeting, and widely throughout economics, it measures the excess or shortfall of cash flows, in present value (PV) terms, once financing charges are met. By definition: NPV = Present value of net cash flows. Present Value in Year 0 (Annual Cash Flow Table) Values in this column are the discounted values of annual cash flows reflecting their values in Year 0 of the study period. Project Briefly describe the project that this alternative is being considered for. Simple Payback Years (SPB) (Output) This calculated output represents the time, in years, that it would take for the initial year annual savings amount to equal the initial incremental cost. Simple Return on Investment (ROI) (Output) This calculated output represents the percentage return on investment that is equivalent to the Simple Payback Years (SPB). It is equivalent to the initial incremental cost divided by the initial year annual savings amount. Study Period (Years) (Input) The period of study for this analysis, in years. Ideally, the study period should approximate the average life expectancy of the alternatives being studied. Total Annual Savings (Initial) (Output) This is the calculated sum of Annual Electricity Savings plus Annual Fuel Savings minus Annual M&O Cost. Total by Year (Annual Cash Flow Table) The calculated outputs in this column represent the total annual cash flows for each year. They are displayed in current dollar values for the year they represent. They are not discounted to the present. Year in Study Period (Annual Cash Flow Table) This column displays a number representing the year in the study period, for which the values on the row in the spreadsheet refer to. refer to.