+1(978)310-4246 credencewriters@gmail.com
Select Page

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
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
PRESENT VALUE
Example #5
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
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 =
Finding N (Nper) Number of Periods (or Years)
\$50
\$100
12%
6.12
Present Value =
Future Value =
Discount Rate =
\$100
\$25
12%
-12.23
Example #7
Present Value =
Future Value =
N (Nper)
Finding I (Interest Rate)
\$100
\$200
5
14.87%
Payment (PMT)
Future Value =
N (Nper)
\$100
\$750
5
20.40%
Example #8
Present Value =
Future Value =
N (Nper)
Interest Rate
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)
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)
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%
(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.
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?
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.