How to Calculate Accounting Terms Using Excel

Only available on StudyMode
  • Download(s) : 45
  • Published : January 23, 2011
Open Document
Text Preview
How to Calculate a Present Value Using Microsoft Excel
I want to do this! What's This?
Using Microsoft Excel to calculate the present value of a potential investment is a simple task once you learn the syntax of the required formula. Follow these easy steps and you can calculate present value using Microsoft Excel easily and quickly.


1. 1
Understand the concept of present value. Present value is one of the Time Value of Money calculations. Use it to answer questions such as, "What is the most expensive car or house I can buy, given the market interest rate and the maximum monthly payment I can afford?" In other words, it is what a given investment is worth today, at this time. 2. 2

Open Microsoft Excel. Click in the cell in which you wish the result of your formula to show. Type the following formula starter: =PV( 3. 3
Observe the screen tip that pops up as soon as you type your opening parenthesis. It looks like this: (rate, nper, pmt, [fv], [type]). This screen tip design will help you know what values to input. 4. 4

Enter the interest rate of your investment, divided by the number of times per year you expect to owe payments. For example, if you can get an interest rate of 6 percent on a car loan paid back monthly, enter ".06/12" (without the quotations) in the rate section of the formula. Type a comma to move to the next section of the formula. 5. 5

Type in the number of periods (nper) you expect to hold the investment. If your proposed car loan is for five years, enter 60 in this field, because the number of periods is 5 years times 12 months, for a total of 60 car payments. Type a comma to move to the next section of the formula.

6. 6
Fill in the highest monthly payment (pmt) you would like to make preceded by a minus sign (since the payment is money leaving your pocket, it is expressed as a negative). If you would like to pay no more than $500 per month, enter "-500" (minus the quotations). Type the ending parenthesis of the equation and press "Enter." Note you do not have to fill in the "fv" or "type" fields; they are optional. 7. 7

Note the results of your equation. If you want to pay $500 per month at 6 percent interest over 5 years, the most expensive car you can buy would cost $25,862.78. Try a second problem to make sure you have the hang of the PV equation in Excel. You want to purchase a house that requires quarterly payments. You want to spend no more than $5,000 per quarter. Prevailing market interest rates are 7.5 percent for a 30-year loan. What is the most expensive house you can afford? Your formula should look like this: =PV(.075/4, 30*4, -5000). You should get $237,968.50 as an answer.

How to Calculate a Net Present Value Using Microsoft Excel

I want to do this! What's This?
Net present value is a tool you can use to evaluate the profitability of an investment or project. It compares the present value of the cash inflow from the investment to its cash outflow, telling you the value of the investment. You can use Microsoft Excel to determine net present value and the quality of the investment you are considering.


1. 1
Do your homework. You will need to know the initial investment required and any additional costs you will be paying. You will also need to know the income you will be earning from the investment and the interest rate your investment will earn. 2. 2

Enter the above data in a column in an Excel spreadsheet. Use negative numbers to indicate the money you are paying, while the income can be left as positive numbers. 3. 3
Go to Excel's function wizard and choose NPV from the financial category. For rate, enter the interest rate as a decimal, not as a percent. Enter all cell descriptors of all the values except the initial cost. For example, [C2:C4]. 4. 4

Subtract your initial investment amount from the amount found using the net present...
tracking img