How to calculate cost benefit analysis in excel

THE FUTURE VALUE FOR A SINGLE PAYMENT Using a Spreadsheet to Calculate Future Value Most spreadsheets have easy-to-use financial functions that can calculate future value. For example, to calculate the future value of $10,000 at 10 percent interest over five years in Excel, use the FV function in the Financial category. Enter the rate (10%), the number of periods (5), and the present value as a negative number (–10,000) to represent the cash outflow of the investment. The future value, $16,105, is automatically calculated at the bottom (see Exhibit 6.5). Note that the future value is a positive number because it represents a cash inflow of the principal and interest at a later point in time. Present Value (PV) The value today of a payment (or series of payments) to be received in the future, taking into account the cost of capital (sometimes called discount rate). It is calculated using the formula: PV = FV × PVF or PV = FV × 1 / (1 + i)n. Present Value of an Amount to Be Received in the Future The focus until now has been on the future value of money invested today. The example showed that $10,000 invested by a clinic today will be worth $16,105 in five years at 10 percent compound interest each year. In this section, the question is turned around to become: How much is $16,105 to be received five years from now worth today? The value today of a payment (or series of payments) to be received in the future, taking into account the cost of capital, is the present value. Taking future values back to the present is also called discounting. Using a Formula to Calculate Present Value Just as the present value is multiplied by a future value factor (FVF) to determine the future value, the future value is multiplied by a present value factor to calculate present value. The present value factor, 1 / (1 + i)n, is the inverse of the future value factor: (1 + i)n. Key Point The present value factor is the reciprocal of the future value factor and is calculated using the formula 1 / (1 + i)n. Key Point Key Point The formula to find the present value is: Present Value = Future Value × Present Value Factor. It is abbreviated as: PV = FV × PVFi,n or PV = FV × 1 / (1 + i)n. Thus, using the formula, the present value of $16,105 at 10 percent interest for five years can be calculated as follows: The 1 / (1.6105), which equals 0.6209, is the present value factor. It can be interpreted to mean that at 10 percent interest, a dollar received five years from now is worth only about 62 percent of its value in today's dollars. In the example, $16,105 received five years from now is worth only $10,000 in today's dollars. Compounding Converting a present value into its future value, taking into account the time value of money (see the previous definition of compound interest method). It is the opposite of discounting. Discounting Converting future cash flows into their present value, taking into account the time value of money. It is the opposite of compounding. Exhibit 6.6 illustrates the relationship between present value and future value. Just as $10,000 today grows to $16,105 over five years at 10 percent compound interest, $16,105 received five years from now is worth $10,000 today, assuming a 10 percent (discount) rate. Key Point To find future value, compound. To find present value, discount. Discounting is the opposite of compounding. Compound toward the future; discount to the present. EXHIBIT 6.6 THE RELATIONSHIP BETWEEN PRESENT VALUE AND FUTURE VALUE AT 10 PERCENT AND 5 PERIODS EXHIBIT 6.7 EXAMPLE OF HOW TO FIND THE PRESENT VALUE FACTOR AT 10 PERCENT INTEREST FOR 5 YEARS, USING TABLE B.3a a The present value factor for 10 percent interest over 5 periods is abbreviated as: PVF10,5. Using Tables to Compute Present Value Just as Table B.1 contains a list of precalculated future value factors (FVFs) based on the formula (1 + i)n, Table B.3 contains a list of present value factors (PVFs), based on the formula 1 / (1 + i)n. (Tables B.2 and B.4 will be discussed shortly.) Finding a present value factor in Table B.3 is analogous to finding a future value factor in Table B.1. The process of locating the present value factor for five years at 10 percent interest is illustrated in Exhibit 6.7. This present value facto ...