Financial Homework

There are 4 methods to do TVM calculations:

1. the longhand method of multiplying exponential formulas

2. using any of the 4 TVM tables

3. using excel

4. using a financial calculator

In my opinion method 1 is too difficult. #2 takes too long; #3 I cannot help anyone with as every different calculator has its own 100 page instruction book

The easiest way is learning to use excel.

A. To use excel, hit the “Fx” toolbar, and choose “financial” functions from the pulldown menu

To do any kind of present value problem, go to the PV function

To do any kind of FV problem, go to the FV function

Whichever function you choose will open a window into which you will type in data

Guidelines to follow:

B. The “rate” means the decimal format of the discount or interest rate PER PERIOD to use; if its 5%, type in .05….if its 12% type in .12.

If instead of annual compounding, for example if problem dealt with semiannual compounding, and annual rate was 10%, you would type in .05.

C. “Periods” means the number of compounding periods. If problem is 10 years, compounded annually, type in 10; if its 10 years compounded semi-annually, type in 20

D. means what the future sum would be.

E. “Payment” field would only be filled in if its an annuity [a stream of equal periodic payments like a car loan or mortgage], in which case you would type in the size of the periodic payment. Otherwise, leave it blank.

F. Generally money paid in [like to a bank], should have a negative sign, and your answer will come out positive then

Specific examples:

1. For determining the PV of some future some, use the PV function, type in your discount rate as a decimal, type in the number of periods, and type in the future value. If its an annuity, type in the $ amount of the periodic payment in the “payment” field.

2. For determining the Future value of some present sum, use the FV function and enter info as above whether its one present sum, or its an annuity stream

3. If instead of a simple end of period annuity problem, its an “annuity due” problem [payment on the first day of the period vs., the last day, like an ordinary annuity], type “1” into the “type” field

4. Determining the effective annual rate: EAR

Go to financial section of Fx toolbar, using pull down window to get “effective”. Type in the nominal or stated ANNUAL percentage rate[APR], as a decimal[12% would be .12] and type in the number of compounding periods per year[if monthly compounding type in 12; if weekly compounding type in 52]. The answer should ALWAYS be >ANNUAL rate you typed in, unless its annual compounding in which case APR=EAR

5. Loan payment: to determine the size of the equal periodic loan payment, use the “payment” function, PMT. Type in the loan interest rate per period, as a decimal; if its an annual 12%, then its 1% per month, for example. Type the value of the money to be borrowed, in PV; type in the number of loan periodic payments. If you want the monthly loan payment, make sure the interest rate is expressed as “per period”

6. For bond YTM problems

Use the “rate” function; type in number of periods, any periodic payment, what you are paying out [with a negative sign], what you get back as FV

7. Valuing a bond:

Time value of money principles can be used to answer the question. “what is the appropriate market price, or value of a certain bond, which has a coupon or interest rate R, a face value of F, and has Y years left to maturity.”

As we go through this you’ll better understand why financial newscasters will frequently say “rising interest rates” cause a fall in bond prices, and lowering interest rates causes an increase in bond pricing-not trivial events when we’re talking about billions of dollars in bonds traded everyday.

The value of a bond is made up of two pieces-the PV of its face value upon maturity and the present value of the stream of coupon or interest payments over its remaining time to maturity.

To determine the PV of a bond you need to determine the PV of each of these components.

The present value of the face value is simply the maturity value ($1000 most of the time) discounted by the number of periods remaining at the appropriate discount rate. Always remember, your investment track record or discount rate may be higher or lower than the other party in the transaction of buying and selling a bond.

The present value of the stream of remaining coupon payments can be determined through a series of individual PV calculations or one annuity calculation

Example:

Maturity=30 years, but 5 years have passed already since the bond was initially issued and purchased so there’s 25 years left

Coupon rate=10% so annual interest payment is $1000 x .1=$100

Face value=$1000

Your personal investment track record of earnings, [or your discount rate]=6%

What’s the bonds appropriate price, or market value or… “PV?

You COULD do this in two pieces…..

a. PV of the face value

I=.06, n=25, PMT=0, FV=1000 equals $233

b. PV of the annuity stream of interest payments=

I=.06, n=25, PMT=10% x 1000=100; FV=0 equals 1278.30

Total PV of these two pieces=$233+$1278.30=$1511

OR, using excel do it in one step

I=.06 n=25, PMT=100, FV=1000

As this is greater than the par or face value, its termed selling or valued or priced…. “at a premium”.

Why would it sell as a premium?

Compare your personal discount rate of 6%, with the coupon interest rate of 10%. You could do much better than usual by buying my bond at 10% interest; in fact its so good a deal that you’re going to be willing to pay me a “premium” to buy it.

Guess what would happen if your discount rate was HIGHER than the bonds coupon rate….yes, its PV would be <$1000, and it would be selling or valued at a “discount”. Why does this make sense?

You’d have to have a very good reasons to buy something, giving you a worse return,( lets say 5%) than your own track record of returns(6%). To convince you to buy it, I’ll have to put it on “sale” by selling it at a discount!

Ok…summarizing using excel to calculate a bonds value

a. go to the normal present value function in excel[PV]

b. in the rate field type in the appropriate discount rate, marketplace interest rates, ytm, expected return.....in decimal format[this is NOT the same as the bonds coupon or interest rate, other than by coincidence]

c. in the number of periods type in the number of periods of compounding

d. in the "payment field" type in the $ value of the interest annuity. Its the stated coupon or interest rate x the bonds face value

e. in the FV field type in the face value which is almost always 1000

Remember, that if bond pays interest more frequently, such as semi-annually, you have to make 3 adjustments to your calculations; yields on similar bonds, or discount rate or YTM rate/2, # of periods x 2, coupon payment/2

CAPITAL BUDGETING

8. For IRR capital budgeting problems:

Enter into a series of excel worksheet cells, the outflow, or initial investment[with a negative], and inflows, in the proper order

Go to IRR function and it will ask for the range of cell containing the cash stream you wish to determine IRR for.

For example if in year0,1,2,3 you have cash flows of -10,000[you invest 10,000 on day 1], and then in years 1,2,3 cash inflows or benefits of 5000,6000,10,000. You would enter -10,000 in cell A1, 5000 in A2, 6000 in A3, and 10,000 in A4[all without commas].

Type in A1:A4 and it will tell you the IRR

Ignore the “guess” field

9. For capital budgeting NPV problems

NPV=PVinflows[or financial benefits]-PV outflow[or the initial investment]

Use excels NPV function

Type in sequentially the year 1,2,3, etc cash inflows or benefits you get each year.

Type in the appropriate discount rate as a decimal

It will give you the PV of the inflows

This will NOT give you the NPV of the project , it will only give you the PV of all the inflows; you STILL have to subtract the investment, or PV of the outflow

4