Sheet 1: TVM Problems

	
	
	
	
	

		
Excel Financial Formulas
		
	

		
Future Value
		
The FV function returns the future value of an investment based on periodic, constant payments and a constant interest rate.
		
	

		
Excel Formula:
		
FV(rate,nper,pmt,pv), where:
		


		


	
	

		


		
rate:
		
is the interest rate per period.
		
	

		


		
nper:
		
is the total number of payment periods in an annuity.
		
	

		


		
pmt:
		
is the payment made each period; it cannot change over the life of the annuity.
		
	

		


		
pv:
		
is the present value, or the lump-sum amount that a series of future payments is worth right now.
		
	

		
Example:
		
Find the ammount accumulated when $1000 is invested for 5 years at 8% compounded quarterly.
		
	

		
Solution:
		


		


		


	
	

		


		
Interest rate:
		
0.08
		


	
	

		


		
Compound Periods:
		
4
		


	
	

		


		
Number of years:
		
5
		


	
	

		


		
rate:
		
0.02
		
= Interest rate / compound periods
	
	

		


		
nper:
		
20
		
= Number of years * compound periods
	
	

		


		
pmt:
		
0
		


	
	

		


		
pv:
		
-1000
		
(note negative sign here)
	
	

		


		
FV(rate,nper,pmt,pv) =
		
$1,485.95 
		


	
	

		


		


		


		


	
	

		
Present Value
		
The PV function returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
		
	

		
Excel Formula:
		
PV(rate, nper, pmt, fv), where
		


		


	
	

		


		
rate:
		
is the interest rate per period.
		
	

		


		
nper:
		
is the total number of payment periods in an annuity.
		
	

		


		
pmt:
		
is the payment made each period; it cannot change over the life of the annuity.
		
	

		


		
fv:
		
is the future value, or a cash balance you want to attain after the last payment is made.
		
	

		


		


		


		


	
	

		
Example:
		
Find the present value of $1000 due after 3 years if the interest rate is 9% compounded monthly.
		
	

		
Solution:
		


		


		


	
	

		


		
Interest rate:
		
0.09
		


	
	

		


		
Compound Periods:
		
12
		


	
	

		


		
Number of years:
		
3
		


	
	

		


		
rate:
		
0.0075
		
= Interest rate / compound periods
	
	

		


		
nper:
		
36
		
= Number of years * compound periods
	
	

		


		
pmt:
		
0
		


	
	

		


		
fv:
		
1000
		


	
	

		


		
PV(rate, nper, pmt, fv) =
		
($764.15)
		


	
	

		


		


		


		


	
	

		
Rate
		
The RATE function returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.
		
	

		
Excel Formula:
		
RATE(nper,pmt,pv,fv), where
		


		


	
	

		


		
nper:
		
is the total number of payment periods in an annuity.
		
	

		


		
pmt:
		
is the payment made each period; it cannot change over the life of the annuity.
		
	

		


		
pv:
		
is the present value, or the lump-sum amount that a series of future payments is worth right now.
		
	

		


		
fv:
		
is the future value, or a cash balance you want to attain after the last payment is made.
		
	

		


		


		


		


	
	

		
Example:
		
Find the nominal rate compounded seminnually for an investment of $500 which amounts to $588.38 in three years. 
		
	

		
Solution:
		
Compound Periods:
		
2
		


	
	

		


		
Number of years:
		
3
		


	
	

		


		
nper:
		
6
		


	
	

		


		
pmt:
		
0
		


	
	

		


		
pv:
		
-500
		
(note the negative sign here)
	
	

		


		
fv:
		
588.38
		


	
	

		


		


		


		


	
	

		


		
RATE(nper,pmt,pv,fv) =
		
2.75%
		


	
	

		


		


		


		


	
	

		
Payment
		
The PMT function calculates the payment or a loan based on constant payments and a constant interest rate.
		
	

		
Excel Formula:
		
PMT(rate,nper,pv,fv), where
		


		


	
	

		


		
rate:
		
is the interest rate for the loan.
		
	

		


		
nper:
		
is the total number of payments for the loan
		
	

		


		
pv:
		
is the present value, or the total amount that a series of future payments is worth now; also known as principal.
		
	

		


		
fv:
		
is the future value, or a cash balance you want to attain after the last payment is made
		
	

		


		


		


		


	
	

		
Periods
		
The NPER function teturns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
		
	

		
Excel Formula:
		
NPER(rate, pmt, pv, fv), where
		


		


	
	

		


		
rate:
		
is the interest rate for the loan.
		
	

		


		
pmt:
		
is the payment made each period; it cannot change over the life of the annuity.
		
	

		


		
pv:
		
is the present value, or the lump-sum amount that a series of future payments is worth right now.
		
	

		


		
fv:
		
is the future value, or a cash balance you want to attain after the last payment is made.
		
	

		


		


		


		


	
	

		
Effective Rate
		
You can use the FV (future value) function to compute the 'effective rate'
		
	

		
Example:
		
What effective rate is equivalent to the nominal rate of 6% compounded semiannually?
		


		


	
	

		
Solution:
		
Interest rate:
		
0.06
		


	
	

		


		
Compound periods:
		
2
		


	
	

		


		
rate:
		
0.03
		


	
	

		


		
nper:
		
2
		


	
	

		


		
pmt:
		
0
		


	
	

		


		
pv:
		
-1
		
(note the negative sign here)
	
	

		


		
FV(rate,nper,pmt,pv)-1 =
		
6.09%
		
(subtract 1 from FV and format as %)
	
	

		
Annuities Examples
		
	

		
Example:
		
Find the present value of an annuity of $100 per month for 3 1/2 years at an interest rate of 6% compounded monthly.
		
	

		
Solution:
		
We use the PV (present value) function as follows:
		
	

		


		
Interest rate:
		
0.06
		


	
	

		


		
Compound Periods:
		
12
		


	
	

		


		
Number of years:
		
3.5
		


	
	

		


		
rate:
		
0.005
		


	
	

		


		
nper:
		
42
		


	
	

		


		
pmt:
		
-100
		
(note the negative sign here)
	
	

		


		
fv:
		
0
		


	
	

		


		
PV(rate, nper, pmt, fv) =
		
$3,779.83 
		


	
	

		


		


		


		


	
	

		
Example:
		
Find the monthly payment for a loan of $10000 for 3 years with an interest rate of 8% compounded monthly.
		
	

		
Solution:
		
We use the PMT (payment) function as follows:
		
	

		


		
Interest rate:
		
0.08
		


	
	

		


		
Compound Periods:
		
12
		


	
	

		


		
Number of years:
		
3
		


	
	

		


		
rate:
		
0.006666666666667
		


	
	

		


		
nper:
		
36
		


	
	

		


		
pv:
		
-10000
		
(note the negative sign here)
	
	

		


		
fv:
		
0
		


	
	

		


		
PMT(rate,nper,pv,fv) =
		
$313.36 
		


	
	

		


		


		


		


	
	

		
Example:
		
Optional: For the previous example, find the payment if the payments are made at the beginning of the month.
		
	

		
Solution:
		
The PMT function (like most others) can take another optional input parameter (0 = payment at end, 1 = payment at beginning of month) as follows:
		
	

		


		
rate:
		
0.006666666666667
		


	
	

		


		
nper:
		
36
		


	
	

		


		
pv:
		
-10000
		
(note the negative sign)
	
	

		


		
fv:
		
0
		


	
	

		


		
PMT(rate,nper,pv,fv,1) =
		
$311.29 
		


	
	

		


		


		


		


	
	

		
Example:
		
How many months will it take to pay off a debt of $1500 where payments of $75 will be made each month and the interest rate is 12% compounded monthly.
		
	

		
Solution:
		
We will use the NPER (Periods) function as follows:
		


		


	
	

		


		
Interest rate:
		
0.12
		


	
	

		


		
Compound Periods:
		
12
		


	
	

		


		
Number of years:
		
3
		


	
	

		


		
rate:
		
0.01
		


	
	

		


		
pmt:
		
75
		


	
	

		


		
pv:
		
-1500
		
(note the negative sign)
	
	

		


		
fv:
		
0
		


	
	

		


		
NPER(rate, pmt, pv, fv) =
		
22.4257418780364
		


	



Sheet 2: Present Value

	
	
	
	
	
	
	

		


		
Calculating Net Present Value in Excel
		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
This worksheet demonstrates examples of using an Excel function to find the net present value of an investment.
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Net present value is calculated using a discount rate (which may represent an interest rate or the rate of inflation) and a series of future payments (negative values) and income (positive values).
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
The NPV formula [NPV(rate,value1, value2,...)]consists of three fields. Rate is the periodic discount rate over the length of the project. Value1 and Value2 are 1 to 29 arguments representing the payments and income. These values must be equally spaced in time and occur at the end of each period. 
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
NPV uses the order of Value1, Value2,… to interpret the order of cash flows. Be sure to enter payment and income values in the correct sequence. Arguments that are numbers, empty cells, logical values, or text representations are counted. Empty cells, logical values, text, or error values are ignored.
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Excel’s method of calculating NPV assumes that the first value occurs one period from the present. Many investments projects call for an immediate initial investment, followed by a stream of cash flows in subsequent periods. If this is the case, you must exclude the initial investment from the NPV calculation in Excel by calculating the NPV of the cash flow stream and subtracting the initial investment from the NPV calculation to get the actual NPV for the project.
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
This example assumes an investment of $10,000 one year from today; an annual income of $3,000, $4,200, and $6,800 in the three years that follow; an annual discount rate of 10%.
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Follow these steps to use Excel's NPV function to find the net present value for an investment:
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		
1)
		
Select the output cell for the solution. (For this example, use cell B57.)
		


		
10%
	
	

		


		


		


		


		


		


		


		


		


		
-10000
	
	

		
2)
		
Click the function button (fx), select All in the left pane to display all Excel functions, and double-click NPV in the right pane. 
		


		
3000
	
	

		


		


		
4200
	
	

		


		


		


		


		


		


		


		


		


		
6800
	
	

		


		
Note: When you click on NPV, the formula is shown at the bottom of the Paste Function dialog box: =NPV(rate,value1,value2,...). 
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		
3)
		
The cursor automatically appears in the "Rate" field, prompting you for the required data. Enter the rate. (For this example, the rate is 10%, or .10.)
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		
4)
		
Enter data for the remaining data fields. For "value1," enter -10000. For "value2," enter 3000. (When you click in the value2 box, a box appears for "value3," and so on) For "value3," enter 4200. For "value4," enter 6800.
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Note: As the cursor moves from field to field, the definition of each respective field is shown at the bottom of the NPV function box.
		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
The formula bar should now contain the formula: =NPV(10%,-10000,3000,4200,6800).
		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		
5)
		
After you have entered the required data, click OK.
		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Excel displays the result in the output cell. The result for this example is $1,188.44. In this example, you include the initial $10,000 cost as one of the values because the payment occurs at the end of the first period.
		


		


	
	

		


		


		


	
	

		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


	
	

		


		
Copyright 2001 UNext.com LLC. All rights reserved.
		


		


		


		


		


		


		


		


	



Sheet 3: NPV Function

	
	
	

		
Net Present Value Function
		
	

		


		


		


		


		


		


		


		


		


		


		


	
	

		
NPV can be easily calculated using the net present value function in Excel.  The NPV function can be found under the financial category.  There is one issue regarding the NPV function, it can only be used on future cash flows.  NPV assumes all cash flows are at the end of the period and therefore discounts all cash flows back to time 0.  If there is a cash outflow at time 0 then it will erroneously discount it.  If a outflow occurs at time 0, it must be added to the discounted future cash flows.  To illustrate this we will use machine B from the previous example.
		
	

		


		


		


		


		


		


		


		


		


		


		


	
	

		
Machine B
		


		


		


		


		


		


		


		


		


		


	
	

		


		


		


		


		


		


		


		


		


		


		


	
	

		


		
0
		
1
		
2
		
3
		
4
		
5
		


		
Discount Rate
		


	
	

		
Cash flows
		
-$75,000
		
$20,000
		
$20,000
		
$20,000
		
$20,000
		
$20,000
		


		
10%
		


		


	
	

		


		


		


		


		


		


		


		


		


		


		


	
	

		
NPV
		
$815.74
		


		
Formula
		
=B8+NPV(I8,C8,D8,E8,F8,G8)