Description

EXCEL FINANCIAL FORMULA EXAMPLES – 15

Example #1:

NET PRESENT VALUE

Discount Rate:

12%

Year 0

-65

Year 1

10

Year 2

20

Year 3

40

Year 4

65

Year 5

-20

ANSWER: NPV (cash flows occur at the BEGINNING of each period)

$18.30

For the NPV example, you don’t include the year 0 cash flow of $65 (cell B2) inside the parenthesis because the payments occur at the BEGINNING of the first period. So the answer is $18.30.

Example #2

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

INTERNAL RATE OF RETURN (IRR)

-65

10

20

40

65

-20

22.41%

Example #3

(Note: Use the yearly data from Example #2)

MODIFIED INTERNAL RATE OF RETURN (MIRR)

12%

15%

18.12%

Example #4

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

PRESENT VALUE

Example #5

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

ANSWER

FUTURE VALUE

12%

5

100

Finance Rate

Reinvestment Rate

(Discount $100 back 5 years at a 12% discount rate)

Discount Rate:

# Periods (or years) being discounted:

FV

$56.74

12%

5

100

(Compound $100 up 5 years at a 12% discount rate)

Discount Rate:

# Periods (or years) being compounded:

PV

$176.23

Example #6

Present Value =

Future Value =

Discount Rate =

ANSWER

Finding N (Nper) Number of Periods (or Years)

$50

$100

12%

6.12

Present Value =

Future Value =

Discount Rate =

ANSWER

$100

$25

12%

-12.23

Example #7

Present Value =

Future Value =

N (Nper)

ANSWER

Finding I (Interest Rate)

$100

$200

5

14.87%

Payment (PMT)

Future Value =

N (Nper)

ANSWER

$100

$750

5

20.40%

Example #8

Present Value =

Future Value =

N (Nper)

Interest Rate

ANSWER

Finding the Payment amount (PMT) or Annuity amount

$0

$100,000

20

12%

$1,387.88

How long would it take to compound $50 up to $100 using a 12% discount rate?

(Or 6.12 years. Note that you have to make either the FV or PV input negative for the formula to work)

How long would it take to discount $100 down to $25 using a 12% discount rate?

Example #9

(Or 12.23 years. Note that years can not be negative. You have to make either the FV or PV input negative for the formula to work)

If you start with $100 and end with $200 after 5 years, what was the annual interest rate earned?

(or 14.87%. You must keep either the Present Value number or Future Value number negative.)

If you receive payments of $100 each year for 5 years and end up with $750 after 5 years, what was the annual interest rate earned?

(or 20.40%. Note that the Payment input or Future Value input must be negative for the formula to work)

What would have to be the annual payment amount (or annuity amount) to have $100,000 after 20 years with a 12% discount rate?

(Note: you want the FV input to be negative so your answer comes out positive.)

SUM, AVERAGE, VARIANCE, STANDARD DEVIATION & CORRELATION

0.12

0.15

0.08

0.06

0.08

0.4900

0.0980

0.0013

0.0363

0.3928

SUM

AVERAGE

VARIANCE

STANDARD DEVIATION

CORRELATION

Example #10

Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent, and a yield to

maturity of 9 percent. If the bond makes semiannual payments, what is its price today?

Settlement

Maturity

Rate

YTM

Redemption

Frequency

Basis

Bond price (% of par):

Multiply by 10

0.09

0.11

0.15

0.03

-0.12

Calculating a Bonds Price

1/1/00

1/1/22

0.08

0.09

100

2

0

90.49

904.91

(Think of Settlement as the beginning of the duration of the bond)

(Think of Maturity as the end of the duration of the bond)

(Coupon Rate)

(Yield to Maturity or Required Rate fo Return)

(Bonds Face Value, Par Value, or Fair Price; Note that is is $100, not $1,000. You make the adjustments by multiplying the answer by 10.)

(Coupon payments are semiannul, so you put in a 2)

(Always leave it blank)

(The answer. But you need to multiply it by 10 to get the actual bond price.)

(Microsoft gives the bond price in 2 digits like in cell B14. You need to multiply it by 10 to get the actual bond price)

(ANSWER = 904.91)

Example #11

Calculating a Bonds Yield to Maturity

Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent and a price of

$960.17. If the bond make semiannual payments, what is its yield to maturity?

Settlement

Maturity

Rate

Pr

Redemption

Frequency

Basis:

Yield to Maturity:

1/1/00

1/1/22

0.08

96.017

100

2

0

8.40%

(Think of Settlement as the beginning of the duration of the bond)

(Think of Maturity as the end of the duration of the bond)

(Coupon Rate)

(The bonds price per $100 face value)

(Bonds Face Value, Par Value, or Fair Price; Note that is is $100, not $1,000. You make the adjustments by multiplying the answer by 10.)

(Coupon payments are semiannul, so you put in a 2)

(Always leave it blank)

(ANSWER = 8.40%)

Example #12

Calculating the Effective Annual Interest Rate

Supose you have a Nominal Interest Rate of 5.25% that is compounded quarterly (4 times) during the year. What is the Effective Annual Interest Rate?

Nominal Interest Rate:

Npery (Number of compounding periods per year)

Effective Annual Interest Rate:

5.25%

4

5.3543%

(ANSWER = 5.35%)

(Note: The EAR is always higher than the Nominal Rate as long as there is more than 1 compounding period per year. If you increase the compounding periods per year, the Effective Annual Rate will increase, but at a decreasing rate).

Example #13

Calculating the Annual Nominal Interest Rate

Supose you have an Effective Annual Interest Rate of 5.35% that is compounded quarterly (4 times) during the year. What is the Nominal Annual Interest Rate?

Effective Annual Interest Rate:

Npery (Number of compounding periods per year)

Nominal Annual Interest Rate:

5.35%

4

5.2459%

Example #14

Calculating the Interest Rate per period of a loan or an investment

If you make monthly payments of 200 on an $8000 loan over 4 years, what is the Annual Interest Rate of the loan?

4

Years of the Loan

-200

Monthly Payment

8000

Amount of the loan

Monthly Interest Rate of the Loan

0.77%

Annual Interest Rate of the Loan

9.24%

Note: Multiply the years of the loan by 12 months for the monthly rate

Note: Multiply the Monthly Interest Rate by 12 to get the annual rate.

(ANSWER = 5.25%)

(ANSWER = .77%)

(ANSWER = 9.24%)

EXAMPLE #15:

CALCULATING THE GEOMETRIC AVERAGE RETURN (OR MEAN)

A stock has produced returns of 14.6 percent, 5.3 percent, 17.6 percent, and -4.7 percent over the past four years, respectively. What is the geometric average return?

Year 1

1.146

Add 1 to all positive returns

Year 2

1.053

Add 1 to all positive returns

Year 3

1.176

Add 1 to all positive returns

0.953

For negative returns, subtract it from 1. You have to do this to keep all data positive.

7.84%

(ANSWER = 7.84%; Note: Place a minus 1 after the formula to get rid of the whole number)

Problems wk 3

Instructions

Ã¢â‚¬Â¢ Please refer to the “Assignments – Use of Excel” resource under the General

Course Content tab for more guidelines on the MANDATORY use of Excel

Ã¢â‚¬Â¢ Complete the PROBLEMS: (Details mandatory for full credit!)

o Chapter 11: Problems: C11: P5, P6, P7

Weekly summary

Weekly Summaries

Submit a Weekly Summary of topics learned, objectives achieved and a conclusion as

to the practical relevance of the subject material covered. The Weekly Summary should

include the following “Five Points” and run about 1-2 pages. You are expected to use

this “Five Points” as “Topic Headings (same Chapter Titles, see numbers 1-6)” and

include a Title and ReferencePage (you can use your Textbook), with your Summary:

General Format (Five Points to be addressed in your Weekly Summary in APA 7.0

format):

1.What are the most important concepts you have learned from this course?

2.What would you recommend to your management/leadership based on these

concepts?

a.(e.g., change of direction, new systems, re-engineering). Think of this from the

perspective perspective that you are the senior executive in the organization with

the authority to impact change.

3.How will these concepts impact you personally and professionally?

4.What is the value-added from these concepts or what difference can these concepts

make to your organization? (e.g., financial savings, productivity improvements,

expanded marketing activities)

5.Personal notes and observations on your individual progress in the course work.

6.Follow this same format every week.

Purchase answer to see full

attachment