Financial managment

Maylat15

TimeValueofMoneySpreadsheet1.xlsx

Home >Business & Finance homework help >Management homework help >Financial managment

General Info - TVM

The Time Value of Money

$100 received today is worth more than $100 received 10 years from today.

NPER

RATE

PMT

$100

$162.89

Why? If you have $100 today, you have the opportunity to invest. Over time you earn interest and maintain the principal. The amount at 10 years will be greater than the value today.

If you were given $100 today, and you invested it at 5%, in ten years you would have $162.89

Things to know about the TVM

When you compute Future Value problems, the interest is compounded. In compounding you earn interest on the principal and interest on the interest from the prior period.

When you compute Present Value problems, the interest is discounted

When valuing and comparing investments managers usually adopt the present value approach.

Different investments may include multiple time periods. We have to put them all in one so we can compare the value of different investments. For example:

Project A covers 5 years, Project B be for 10 years and Project C will last 30 years.

If you calculated the FV of each investment, you would have a FV amount for year 5, one for year 10, and one for year 30

We can't compare an amount received in 5 years to an amount received in 10 years, or 30 years. It is like comparing 100 euros to 100 pesos to 100 pounds

However, you can compare the PV of each of the investments, which is the investment's value today.

Knowing todays value of each investment, lets you compare the three different investments in the same time period.

Cash Flow Types

In EXCEL and for a calculators all cash flow is not positive. If you invest, money is out of your pocket and is a negative cash flow. Therefore, if the PV is negative (invest) then the FV should be positive (cash flow you receive). When you get a loan, the initial cash flow is positive, the payments are negative, and the FV is 0 (if it is an amortized loan) or negative if you still owe money. Incorrect signs will give you an error or erroneous result.

Single Amounts - This is like a savings account, a CD or a one time investment.

You have one amount that is either compounding or being discounted.

There are no PMTs (payments).

It's the example above, when you put $100 in a savings account earning 5% for 10 years - you end up with $162.9

Annuities - This is a stream of equal periodic cashflows over a specified period.

Your car payments are a type of an annuity, bonds are also annuities

There are two kinds of annuities, depending when the payments are made.

$220.01

When the PMT comes at the beginning of the period - its an annuity due

$220.01

When the PMT comes at the end of the period - it is an ordinary annuity

Ordinary annuities are the most common

In a single amount, like mentioned above, the interest is compounded each period and added back into the investment

In an annuity, the same amount of payment is made at the same interval.

For example you want to have $1000 at the end of 5 years. How much would to have to invest every year at 5% interest? At the end of year 1 you invest $181.

Annuity

Payments at the End of Year

NPER

($181)

RATE

$181

Payment in year 5 is made at end of year, when amount is withdrawn. Therefore, it does not earn any interest in year 5

PMT

($181)

$190

FV of payment in year 4

$200

FV of payment in year 3

$1,000

$210

FV of payment in year 2

$220

FV of payment in year 1

$1,000

total value of all the payments at the end of year 5

An annuity due is when the payments are made at the beginning of the period. You have the entire period to earn interest. Lets make the same payments but at the beginning of each year.

Since you have an extra year to earn interest you will earn more money.

Payments at the Beginning of Year

Annuity

($181)

NPER

$190

The payment in year 5 is made at the beginning of the year. Therefore it earns interest during the year

RATE

$200

FV of payment in year 4

PMT

($181)

$210

FV of payment in year 3

$220

FV of payment in year 2

$1,050

$231

FV of payment in year 1

$1,050

total value of all the payments at the end of year 5

In EXCEL an ordinary annuity would be indicated by a 0 under (type).For an annuity due you would put a 1 under type. FV = (rate,nper,pmt,PV, type)

A car or house payment are set up on an amortization schedule so you are paying back some principle, and interest.

It is an annuity because it meets the definition of an investment with equal periodic cashflows for a specified period. Most amortized loans have a FV of 0. So when the payments end, the principal amount borrowed is paid off.

Uneven Cash flows - is different payments or time periods.

These cannot be computed as an annuity in which each PMT is the same

Therefore, to compute uneven cash flows, you can use the NPV or Net Present Value formula

The NPV formula will compute the PV of the uneven cashflows. Note that EXCEL considers the first payment to be at the end of the 1st period. Therefore it does not calculate the initial investment at time 0.

If you need the FV of a set of uneven cashflows, then you need to add an additional step.

First compute the NPV

Then use the NPV amount as the PV in a FV formula

Calculate the PV and FV of the following cash flows that are earning 8.9% interest.

RATE

8.90%

Present Value

Year 1

5000

Compute NPV

$17,117.55

Year 2

3000

=NPV(J73,J74:J80)

Year 3

3500

For the FV, take the PV of all the cash flows to calculate its FV.

Year 4

4125

CPT FV

$31,091.15

=FV(J73,7,,-E75)

Year 5

5525

One formula

$31,091.15

=FV(J73,7,0,-NPV(J73,J74:J80))

Year 6

326

Year 7

1000

Compounding more frequently

Interest rates are stated as yearly rates

If a problems does NOT mention a compounding period - then you can assume it is yearly

However, many investments are compounded more frequently that that, so you need to know how to make adjustments for more frequent compounding

The more frequent the compounding, the more interest is earned

To convert a formula to more frequent compounding three things need to be adjusted:

NPER - short for the number of periods-the number of years needs to me multiplied by the number of compounding periods

PMT - The yearly payment needs to be divided by the compounding periods

RATE - the yearly interest also needs to be divided by the compounding periods

Note: When computing the RATE formula, you adjust the NPER and PMT to the compounding period. Therefore the rate you compute is for that compounding period. If you are computing a monthly rate, then you will have to multiply the rate by 12 to have an annual rate.

You will then need to multiply the answer you get by the compounding period to bring it back to a yearly rate

Because interest rates are ALWAYS stated as yearly rates

Perpetuities

Preferred Stock

A perpertuity is an investment that goes on infinately.

Preferred stock is valued the same as a perpetuity

So how is this possible? An investment that can go on forever????

The payment is the yearly dividend, which is usually a percentage of the par price

First, lets go back to the annuity.

The principal is the purchase price, which remains in tact indefinately,

One of the most common forms of an annuity is a car loan.

or until the preferred stock is sold or the corporation ceases to exist.

You have the value of the car and the interest divided into equal payments.

You should also know that in general - most corporations do NOT issue preferred stock.

So the principal (price of the car) is there at the beginning when you get the loan from the bank.

Then every month for the period of the annuity, you make equal payments that include part principal and part interest.

At the end of the investment period, you will have the car paid off, so the balance of the annuity will be 0

The key here is that each month the annuity is paying out part of the principal and part of the interest.

The difference in the annuity and the perpetuity is that the principal stays put in the perpetuity. It is not paid out each period.

For example, the principal of the perpetuity is $20,000.

In each period the payment will only be the interest that the $20,000 earned over that period.

Therefore, the $20,000 stays in the account, and each period the investor will receive an interest payment.

Because the principal is never touched, that's how a perpertuity can go on forever.

If the owner of the $20,000 perpertuity ever decided to end or cash in the perpetuity, he would receive the $20,000 (principal) back.

General Info - Bonds

	Bonds
	Bonds are long-term debt instruments
	When a company issues bonds - they are actually borrowing money from investors (whoever buys the bonds)
	When a company buys bonds from other companys - they are investing (loaning money to the company that sold the bonds).
	The Bond market is very important! The risk-free market rates are set by the current price of government bonds (treasury bonds)
		The current interest rates in the US are set by the bond market
		Bonds are generally sold in $1000 increments. This is why the standard Par price of a bond may be $1000 $5000 or $10,000 etc.
		For this course we will generally use $1000 as the par value
	How a standard bond works:
	You buy a bond,
	Then you receive annual payments based on the initial or coupon interest rate you were given at the time of purchase
	At the end of the bond term (when the bond matures) you get your initial investment of $1000 back.
	So, if you buy a 5 year bond with an initial interest rate of 10%:
	You pay the current price of the bond
	You receive 5 yearly payments of $100 ($1000 * 10%)
	Then at the end of the 5 years, you receive the par value of the bond or $1000.
	If you hold the bond for the full term, then nothing will change
	Most bonds are NOT held for the full term
	They are bought and sold through investment agencies
	When bonds are sold or re-sold, the current market interest rate may be different from the coupon rate
	If this happens, then the price of the bond will need to be adjusted to make-up for the change in the interest rate
	When the rate goes up - the price of the bond will go down
	When the rate goes down - the price of the bond will go up
	This difference is what you are calculating when you are asked to value a bond
	Things to remember when valuing a bond:
	PMT - the bond payment is calculated by multiplying par vavlue ($1000 for example) by the Coupon Rate - You have to do this when the PMT is not given to you in the question!
	FV - in your valuation computations, the FV is ALWAYS the par value.
	ALWAYS use the Market Price and/or the Market RATE in your formulas
		If you use the coupon RATE in your formula - you will not calculate the value of the bond
		If you use the coupon Price as the PV in your formula - you will calculate the Coupon RATE
		You are not being asked to compute the coupon rate or price - you are being asked to compute the new Market RATE/Price
		The Coupon RATE and Price are ONLY used to calculate the PMT
	If the bond is compounded more frequently than a year - you have to adjust the: NPER, RATE and PMT to the compounding period
		Leaving your imputs as a yearly amount and then multiply/dividing the answer by the compounding period DOES NOT WORK
		When you do this - you are still computing yearly interest
		You have to adjust the NPER, RATE and PMT to the compounding period to compute the correct amount
	When inputing your PV, FV and payment think of if the cash is going out of your pocket or in your pocket. If you are investing, the cash flow is negative since it is going out of your pocket. If you receive the payment and the FV those are positive. You can't have all positive cash flows.

Part 1

Assignment 2 - Part 1 - Worksheet - 20 Points

This worksheet was designed to teach you how to work TVM, Stock and Bond problems and equations in Excel

Worksheet Instructions: This is a tool to help you learn how to enter formulas into excel to solve TVM problems. Since the goal is for you to learn the formulas and how to enter them, you are given the answers. You should enter formulas until you get the correct answer. (Your answer should match mine). This gives you practice on using EXCEL. In the first part of each section, I give you several examples of problems and how to solve them. Then in the second part, you solve problems for youself. I'm providing the answer, so you know if you got it right or not. If you did not get the correct answer, try again. You will be graded on the formula that you have entered. Please fill in the standard formats, then enter the formula in the blue cell. Enter each formula manually, do NOT cut and paste formulas from other spreadsheets or from examples. The purpose of this exercise is for you to learn how to manually enter the formulas, so that you will be able to do it on the exam. There will be several TVM problems on exams. The more you practice, the easier it gets.

Section 1: Solving TVM problems for Single Amounts - Use this method for Savings Accounts, Growth Rates, Interest Rates, Inflation Rates

Things to know:

Standard Format >>>

NPER (N)

Time Value of Money Formulas

*One of the inputs needs to be negative - look at the formulas below

RATE (I/Y)

Future Value

=FV (rate, nper, pmt, pv)

generally, when you pay money, enter as negative, when you receive money, enter as positive

Present Value

=PV (rate, nper, pmt, fv)

*When entering the RATE - don't forget to enter the % sign

PMT

Rate

= RATE (nper, pmt, pv, fv)

*There are NO payments for single amount problems,

Number of periods

= NPER (rate, pmt, pv, fv)

the interest goes back into the account each period, this is called compounding.

Compute ?

Formula goes here!

Payment

= PMT ( rate, nper, pv, fv)

*Once you start entering the formula, a pop-up box will appear showing you the formula.

*When the pop-up box appears, you can also click the fx in the top taskbar, for help entering formulas.

Examples

The future value of $100 received today and deposited at 6 percent for four years is

The present value of $100 to be received 10 years from today, assuming an opportunity cost of 9%

The current dividend is $5, the dividend 6 years ago was $1.95, compute the growth rate

With an interest rate of 3.5%, how long will it take for my $500 investment to triple?

NPER (N)

RATE (I/Y)

3.5%

100

1.95

500

PMT

100

1500

<<< 3 * 500

Compute ?

$126.25

Compute ?

$42.24

Compute ?

16.99%

Compute ?

31.94

Years

=FV(D18,D17,,-D19) or

=-PV(G18,G17,,G21)

=RATE(K17,,-K19,K21)

=NPER(N18,,-N19,N21)

=FV(6%, 4, 0, -100)

= -PV (9%, 10, 0, 100)

=RATE (6, 0, -1.95, 5)

=NPER (3.5%, 0, -500, 1500)

OK, now enter your formula in the blue cells.

The future value of $975 received today and deposited at 3.5% for four years is

The present value of $1285 to be received 10 years from today, assuming an opportunity cost of 2.9%

The current dividend is $9.5, the dividend 4 years ago was $4.95, compute the growth rate

With an interest rate of 8%, how long will it take for my $250 investment to quadruple?

NPER (N)

RATE (I/Y)

PMT

Compute ?

$1,118.83

Compute ?

$965.49

Compute ?

17.70%

Compute ?

18.01

Section 2: Computing Annuities - problems that have equal periodic payments - Car loans, Bonds, Lotto winnings, Retirement accounts . . .

Examples

The future value of $100 received annually for 35 years with 3.8% interest

The present value of $100 to be received semi-annually for 20 years with a 7.5% rate

Beginning TODAY, you deposit $1000 per year for the next 25 years with a 4.8% interest rate - how much will you have in 25 years?

You won the lottery and beginning today, you will be paid $500,000 per year for 50 years. At a 3.5% rate, was is the PV of this prize?

NPER (N)

RATE (I/Y)

3.8%

RATE (I/Y)

7.5%

RATE (I/Y)

4.8%

RATE (I/Y)

3.5%

PMT

100

PMT

100

PMT

1000

PMT

$500,000

Compute ?

$7,076.29

Compouning period

Compute ?

$48,660.66

Compute ?

$12,138,282

=-FV(D44,D43,D46,D45)

Compute ?

$2,055.10

=FV(K44,K43,-K46,K45,1)

=-PV(N44,N43,N46,N47, 1)

=-FV(3.8%, 35, 100, 0)

=-PV(G44/2,G43*2,G46)

=FV(4.8%,25,-1000,0,1)

=-PV(3.5%, 50, 500000, 0, 1)

= -PV (7.5%/2, 20*2, 100)

Things to know: There are two types of annuities: ordinary and annuity due

Beginning TODAY, you deposit $1000 per month with a 3.5% interest rate - how much will you have in 25 years?

When payments come at the end of the period, its an ordinary annuity

When payments come at the beginning of the period, its an annuity due, you need to add a 1 at the end of the formula to compute an annuity due

NPER (N)

This only applies for annuities. If the problem is not an annuity, then the 1 at the end of the formula will give you an incorrect answer.

RATE (I/Y)

3.5%

If payments occur more frequently than 1 year, then you will need to adjust for the compounding.

If you are given a monthly payment amount, that means the interest is being compounded monthly

PMT

1000

You will need to adjust the NPER and RATE for monthly compounding. You can make the adjustment within the EXCEL formula.

You CANNOT multiply the PMT by 12, compute the formula, then divide you answer by 12

Compute ?

$479,963.40

This method might be faster, but it still computes yearly compounding.

=FV(N55/12,N54*12,-N57,N56,1)

=FV(3.5% / 12, 25 * 12, -1000,0,1)

For more frequent compounding, always adjust: NPER, RATE and PMT to the new compounding period.

PMT is monthly - that means monthly compounding.

Now its your turn

The future value of $5000 received annually for 20 years with 2.5% interest

The present value of $5000 to be received annually for 20 years with a 2.5% rate

Beginning TODAY, you deposit $100 per month with a 4.8% annual interest rate - how much will you have in 25 years?

NPER (N)

RATE (I/Y)

PMT

Compute ?

$127,723.29

Compute ?

$77,945.81

Compute ?

$58,035.70

Section 3: Computing Uneven Cash Flows (Mixed Streams)

Your turn - please compute the following mixed stream

Compute the PV and FV of the streatm of cash flows with a 3.8% rate.

CASHFLOWS

Compute the PV and FV of the streatm of cash flows with a 5.1% rate.

CASHFLOWS

NPER (N)

Year 1

2563

NPER (N)

Year 1

1585

RATE (I/Y)

3.8%

Year 2

2568

RATE (I/Y)

Year 2

1585

PV Compute NPV

$17,419.09

=NPV(3.8%,G81:G86)

Year 3

3500

PV Compute NPV

$8,454.09

Year 3

1605

PMT

Year 4

3500

PMT

Year 4

1650

Year 5

3885

Year 5

1800

Compute ? FV

$21,787.61

=FV(D82,D81,0,-D83)

Year 6

4000

Compute ? FV

$11,394.18

Year 6

1850

One formula

$21,787.61

=FV(D82,D81,,-NPV(D82,G81:G86))

Section 4: Computing Perpetuities

Your turn - compute the following perpetuity questions

PV = CF / RATE

Compute PV of a perpetuity with a Cash Flow of $180 discounted at 5.9% >>>>>>>>>>>>>>

PV = 180 / 5.9% = $3050.80

Compute the present value of a perpetual income stream of $500 at a 14 percent discount rate.

RATE = CF / PV

Compute the RATE of a perpetuity with a PV of $3050.80 and a CF of $180 >>>>>>>>>>>>>

RATE = 180/3050.80 = 5.9%

$3,571.43

CF (PMT) = PV * RATE

Compute the Cash Flow of a perpetuity with a PV of $3050.80 earning 5.9% >>>>>>>>>>>>

CF = 3050.8 * 5.9% = $180

What is the rate of interest being earned on a $25,000 perpetuity with a cash flow of $875

3.50%

Section 5: Valuing Bonds - Read about Bonds on the "General Information" tab

Valuing Bonds with more frequent compounding

Examples

What is the current price of a $1000 par value bond maturing in 12 years with a coupon rate of 14% and a YTM of 13%?

What is the YTM (RATE) for a bond currently selling for $1,120 that matures in 6 years with a coupon rate of 8%?

What is the current price of a SEMI-ANNUAL bond maturing in 12 years with a coupon rate of 14% and a YTM of 13%

What is the YTM for a bond currently selling for $1,120 that matures in 6 years with a coupon rate of 8% and monthly compounding?

NPER (N)

RATE (Coupon)

14.0%

RATE (Coupon)

8.0%

RATE (Coupon)

14.0%

RATE (Coupon)

8.0%

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PMT

$140

=C99*C100

PMT

= 8% * 1000

PMT

$140

PMT

RATE (Market Rate)

13%

RATE (Market Rate)

13%

RATE (Market Rate)

PV (Market Price)

$1,120

PV (Market Price)

$1,120

$1,000

Compute ?

$1,059.18

Compute ?

5.59%

Compounding period

=-PV(D105,D101,D104,D107)

=RATE(G101,G104,-G106,G107)

Compute ?

$1,059.95

Compute ?

5.64%

=-PV( 13%, 12, $140, 1000)

=RATE( 6, 80, -1120, 1000)

=-PV (K105 / 2, K101*2, K104/2 ,K107)

=RATE(N101*N108,N104/N108,-N106,N107)*12

= -PV( 13%/2, 12 * 2, $140 / 2, 1000)

=RATE( 6 * 12, 80 / 12, -1120, 1000) * 12

Note: The change for more frequent compounding doesn't change the outcome a whole lot

This is why using multiple decimal spaces is VERY important

Note: When using the RATE formula, I multiplied the whole equation by 12

This is because when I entered the NPER and PMT as monthly amounts,

I computed a monthly Interest RATE - so I multiplied it by 12 to bring it to a yearly rate.

Interest rates are ALWAYS stated as yearly rates.

Remember: When adjusting for more frequent compounding, you must adjust:

RATE, NPER and PMT

OK - Your turn to compute the value of Bonds

What is the current price of a bond maturing in 12 years with a coupon rate of 14% and a YTM of 13%

What is the YTM for a bond with a current price of $908, a coupon rate of 11%, par value of $1000, and 8 years to maturity?

What is the PV of a bond with a current rate of 12%, a coupon rate of 9%, 10 years to maturity, and monthly compounding?

Nico Corp has issued bonds bearing a coupon rate of 12%, pays coupons semi-annually, has 3 years remaining to maturity, and are currently priced at $940. What is the YTM?

NPER (N)

RATE (Coupon)

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PV (Coupon Price)

$1,000

PMT

RATE (Market Rate)

PV (Market Price)

$1,000

Compute ?

$1,059.18

Compute ?

12.91%

Compounding period

Compute ?

$825.75

Compute ?

14.54%

End of Part 1 - Worksheet

Worksheet Grade

20 Possible Points

Part 2

Assignment 2 - Part 2 - Problems - 80 Points

Please complete the Worksheet on "Part 1" first.

Grade for Assignment 2

Questions

Points

Score

Problem 1: On the day you were born, your parents took $8952 to the bank and purchased a 25 year CD that will earn 3.4% interest. How much money will you have on your 25th birthday?

Problem 2: Congratulations! You just won the lottery. Beginning today, you will receive $5000 per week for the next 30 years. Assuming an interest rate of 2.75%, what is the Present Value of this prize?

Part 1

Worksheet Grade

Part 2

NPER

I/Y (Rate)

PMT

Note:

The compounding periods

Compounding Periods

5 Points

is how many times the

CPT (Compute) ?

<<<Please enter your formulas in the blue boxes>>>

CPT (Compute) ?

5 Points

P10

investment compounds per year.

DO NOT CUT & PASTE FORMULAS - ENTER THEM MANUALLY

P11

P12

Problem 3: How many years will it take for an intial investment of $2000, earning 5.4% annually, to reach $10,000?

Problem 4: You have future plans to buy a house 5 years from now. You estimate that a down payment of $20,000 will be required at that time. To accumulate that amount, you want to start making monthly payments into an account paying 3.9% interest. What will your monthly payments be?

Total

100

Total Grade

100

NPER

I/Y (Rate)

PMT

Compounding Periods

5 Points

CPT (Compute) ?

5 Points

Problem 5: You are buying a car. The one you have choosen to purchase is going to cost you $32,985. Your car salesman has told you that you can purchase this vehicle for $525 per month for 72 months. What interest rate will you be paying?

Problem 6: You are not quite sure about the car deal in Problem 5. So the car salesman now tells you that the company is offering a bonus if you buy the car today. You can either choose to get a $2500 discount on the car price, or zero percent financing. Which option is the best deal? Please compute the PMT for both options to find out.

Cash back deal

Zero financing deal

NPER

Note: For a Loan the Price is listed as PV

NPER

I/Y (Rate)

Because the bank gives you the money in the beginning

I/Y (Rate)

FV is 0, because the loan will be paid off at the end

PMT

Compounding Periods

5 Points

CPT (Compute) ?

7 Points

Remember, interest rates are ALWAYS stated as yearly rates.

See "Compounding More Frequently" on the General Information Tab

Problem 7: If the cash flows listed below were deposited at 4.1% interest, what would the present value be?

Problem 8: Find the future value of the same stream of cash flows, assuming that the firm's opportunity cost is 5.5%.

NPER

Year

Amount

NPER

I/Y (Rate)

22,500

I/Y (Rate)

23,295

PMT

23,750

PMT

24,505

Compounding Periods

25,000

Compounding Periods

5 Points

CPT (Compute) ?

7 Points

Problem 9: You are interested in buying a bond that has a coupon rate of 9%, and matures in 25 years. The market rate for bonds with similar risk is 11.5%. What is the most you should pay for this bond?

Problem 10: Gwenyth just purchased a bond for $1250 that has a maturity of 10 years and a coupon interest rate of 8.5%, paid annually. What is the YTM of the $1000 face value bond that she purchased?

NPER

RATE (Coupon)

PV (Coupon Price)

PMT

RATE (Market Rate)

PV (Market Price)

Compounding Periods

8 Points

Compute ?

P10

8 Points

Problem 11: Micron issues a 9% coupon bond with a maturity of 5 years and monthly interest payments. The face value of the bond, payable at maturity, is $1000. What is the value of this bond if your required rate of return is 12%

Problem 12: What is the YTM of a bond that has a current price of $1128, coupon rate of 7.3%, $1000 par value, interest paid semi-annually, and nine years to maturity?

NPER

RATE (Coupon)

9.00%

RATE (Coupon)

PV (Coupon Price)

PMT

RATE (Market Rate)

12%

RATE (Market Rate)

PV (Market Price)

P11

10 Points

1,000

P12

10 Points

Compounding Periods

Compute ?

-$910.09

Compute ?

Please complete the following problems You are REQUIRED to use Excel to compute your answers