Calculating Payback and Profitability
Complete a series of five problems in which you discount the cash flows of alternative capital
investments, compare the expected values of alternative investments, and choose the investment
that will provide maximum value for shareholders.
Note: The assessments in this course build upon each other, so you are strongly encouraged to
complete them in sequence.
Suggested Resources
The resources provided here are optional. You may use other resources of your choice to prepare
for this assessment; however, you will need to ensure that they are appropriate, credible, and
valid. They provide helpful information about the topics in this unit. The MBA-FP6016 –
Finance and Value Creation Library Guide can help direct your research. The Supplemental
Resources and Research Resources, both linked from the left navigation menu in your
courseroom, provide additional resources to help support you.
The following resources will provide assistance to complete the assessment.
• Assessment Problems – Helpful Tips [DOCX].
• Excel Examples [XLS].
The following texts are designed to assist learners to master core concepts, solve financial
problems, and analyze results.
• Boundless. (n.d.). Boundless finance. Retrieved from
https://www.boundless.com/finance/textbooks/boundless-finance-textbook/
• Chapter 5, "Time Value Money".
• Chapter 6, "Bond Valuation"
• Chapter 7, "Stock Valuation".
• Chapter 15, "Dividends".
1. Assessment Instructions
• Demonstrate your understanding of financial concepts by completing the following
problems. Where appropriate, show or explain your work. You may use Excel to work on
the problems.
Problem 1. Calculating net present value (NPV): Porter Incorporated has two exclusive projects,
listed in the table below. Use the NPV rule to rank these two projects. If the appropriate discount
rate is 13 percent, which project should be chosen?
Problem 1. Calculating NPV
Year
Project A
Project B
0
−$12,700
−$9,400
1
$7,000
$4,800
Year
Project A
Project B
2
$5,500
$3,750
3
$2,500
$3,400
Problem 2. Calculating payback period: An investment project provides cash inflows of $920
per year for eight years. Calculate the project's payback period if the initial cost is each of the
following:
• $4,500.
• $5,500.
• $7,000.
Problem 3. Calculating internal rate of return (IRR) for cash flows: Calculate the internal rate of
return for the cash flows of the two projects in the table below.
Problem 3. Calculating IRR for Cash Flows
Year
Project A
Project B
0
−$4,600
−$3,500
1
$1,400
$1,250
2
$2,200
$1,800
3
$2,700
$1,600
Problem 4. Calculating profitability index of a project: Jeff plans to open a small health club.
The equipment will cost $225,000. Jeff expects that there will be after-tax cash inflows of
$62,000 annually for seven years. The equipment will then be scrapped and the health club will
close. At year-end of the first year, the first cash inflow occurs. The required return is 13 percent.
What is the project's profitability index? Should it be accepted?
Problem 5. Calculating project NPV: Jenny's Creamery is considering the purchase of a $27,000
ice cream maker. The ice cream maker has an economic life of eight years. Using the straightline method, it will be fully depreciated. The machine will produce 250,000 servings per year,
with each costing $1.25 to make, and priced at $1.99. The discount rate is 12 percent. The tax
rate is 35 percent. Should the company make the purchase? Provide a rationale using the
calculations.
Calculating Payback and Profitability
Scoring Guide
Criteria
Calculate the payback period and
net present value (NPV) of a
Proficient
Distinguished
Calculates the payback period and Calculates the
net present value (NPV) of a
payback period
Criteria
project.
Proficient
project.
Distinguished
and net present
value (NPV) of a
project; arrives at
accurate
calculations to
recommend
whether to
purchase or not.
Calculate the internal rate of return Calculates the internal rate of
(IRR) for the cash flows.
return (IRR) for the cash flows.
Calculates the
internal rate of
return (IRR) for
the cash flows;
uses appropriate
methods to arrive
at accurate
calculations.
Calculate the profitability index of a Calculates the profitability index
project.
of a project.
Calculates the
profitability index
of a project; uses
appropriate
methods to
calculate the
profitability index.
MBA-FP6016
Assessment Problems – Helpful Tips
Assessment 2 – Financial Statements
Use the inputs provided in the Excel spreadsheet to come up with formula inputs.
Basic accounting equations and formulas:
• 𝐴𝑠𝑠𝑒𝑡𝑠 − 𝐿𝑖𝑎𝑏𝑖𝑙𝑖𝑡𝑖𝑒𝑠 = 𝐸𝑞𝑢𝑖𝑡𝑦. This formula can be manipulated with simple algebra to
place any of the three inputs separately on one side of the equation.
• The equity multiplier formula starts out as 𝑇𝑜𝑡𝑎𝑙 𝐴𝑠𝑠𝑒𝑡𝑠⁄𝑇𝑜𝑡𝑎𝑙 𝐸𝑞𝑢𝑖𝑡𝑦, but it is derived
into the following formula: 1 + 𝐷𝑒𝑏𝑡 − 𝐸𝑞𝑢𝑖𝑡𝑦 𝑅𝑎𝑡𝑖𝑜.
• The return on equity formula is 𝑁𝑒𝑡 𝐼𝑛𝑐𝑜𝑚𝑒⁄𝑇𝑜𝑡𝑎𝑙 𝐸𝑞𝑢𝑖𝑡𝑦, but it can be derived into the
following formula: 𝑅𝑂𝐴 × 𝐸𝑞𝑢𝑖𝑡𝑦 𝑀𝑢𝑙𝑡𝑖𝑝𝑙𝑖𝑒𝑟.
Assessment 3 – Calculating Financial Values
For all of the problems, be sure to use the correct built-in Excel formulas to derive your
answers. Do not use algebra.
Assessment 4 – Calculating Payback and Profitability
Problem 1: When you calculate NPV using the built-in NPV formula in Excel, be sure to place
the year 0 cash outflow outside of the parentheses in the formula because the payment
occurs at the beginning of the first period.
Problem 2: The payback period formula is the amount of time it takes to recover the cost of
the project or investment. The formula to use is 𝐶𝑜𝑠𝑡 𝑜𝑓 𝑃𝑟𝑜𝑗𝑒𝑐𝑡⁄𝐴𝑛𝑛𝑢𝑛𝑎𝑙 𝐶𝑎𝑠ℎ 𝐼𝑛𝑓𝑙𝑜𝑤𝑠.
Problem 4: When you find the NPV as the first step of calculating the profitability index, be
sure to exclude using the year 0 cash outflow (initial cost), using the built-in NPV formula in
Excel. When calculating the profitability index, make sure the year 0 initial cost is a positive
number.
Problem 5: When calculating the operating cash flow, use this formula:
𝑂𝐶𝐹 = (𝑆𝑎𝑙𝑒𝑠 − 𝐶𝑜𝑠𝑡𝑠) × (1 − 𝑇𝑎𝑥 𝑅𝑎𝑡𝑒) + 𝐷𝑒𝑝𝑟𝑒𝑐𝑖𝑎𝑡𝑖𝑜𝑛 × 𝑇𝑎𝑥 𝑅𝑎𝑡𝑒
First, you will have to multiply a few of the inputs given in the assessment to come up with
some of the formula inputs. For the first part of the formula, you need to multiply the number of
servings produced per year by the price of each ice cream serving to derive the sales, which
are not given in their entirety in the problem. You need to find this in order to derive the costs
and depreciation.
Assessment 6 – Calculating Risks and Returns
Problem 8: Under CAPM, the cost of equity (or expected return on equity) formula is:
𝑅𝑖𝑠𝑘 𝐹𝑟𝑒𝑒 𝑅𝑎𝑡𝑒 + 𝐵𝑒𝑡𝑎 × (𝐸𝑥𝑝𝑒𝑐𝑡𝑒𝑑 𝑅𝑒𝑡𝑢𝑟𝑛 𝑜𝑛 𝑡ℎ𝑒 𝑀𝑎𝑟𝑘𝑒𝑡 − 𝑅𝑖𝑠𝑘 𝐹𝑟𝑒𝑒 𝑅𝑎𝑡𝑒)
1
MBA-FP6016
Assessment 7 – Dividends and Stocks
Problem 2: If the stock split increases the number of shares, then the stock price has to be
lower than it originally was. If it is a reverse stock split, where number of shares decrease,
then the stock price must be higher than it originally was.
Problem 3: For this problem, rearrange what is known as the Lintner formula. You want to
isolate Div1 (the dividend one year from now) so the formula should look like the following:
Div1 = Div0 + s*(t *EPS1 – Div0) (Note: The * indicates multiplying.)
𝐷𝑖𝑣1 = 𝐷𝑖𝑣0 + 𝐴𝑑𝑗𝑢𝑠𝑡𝑚𝑒𝑛𝑡 𝑅𝑎𝑡𝑒(𝑃𝑎𝑦𝑜𝑢𝑡 𝑅𝑎𝑡𝑖𝑜 × 𝐸𝑃𝑆1 − 𝐷𝑖𝑣0)
Assessment 8 – Megaware Case Study
Problem 5: Use what you know about the situation and the theory to come up with an answer
that seems appropriate according to the theory. Do not focus on having all exact numbers to
accomplish everything. The final number is not what is important. What does each factor
stand for and what happens if one chooses one of the possibilities for each factor individually?
𝑃0 = 𝐸1(1 − 𝑏)
𝑅𝑠 = 𝑅𝑂𝐸 × 𝑏
What each variable stands for is known in problem 5:
• b = retention ratio.
• E1 = earnings next year.
• ROE = return on equity.
• Dividend-payout ratio = 1 minus b.
• P0 = dividend next year; the earnings next year times 1 minus the retention ratio.
• Rs = sustainable growth rate; the return on equity times the retention ratio.
Eight years in from the start of the company, the profit is $42 million from the sale of a fixed
asset.
• What happens to P0 and ROE if all other factors remain steady and b goes up or
down?
• What effect does the one-time influx of $42 million have on the formula?
Assessment 9 – Financing and Exchange Rates
Problem 1: When calculating the cash for this problem, use the following given data to derive
the answer:
• Net worth.
• Long-term debt.
• Net working capital (excluding cash).
• Fixed assets.
Problem 2:
• For the first calculation, just find the dollar value of the current shares outstanding and
add that to the value for the rights offering using the given data.
• For the second calculation, you will use the number of shares outstanding and number
of new shares outstanding in the future (rights offering) to derive the answer.
2
MBA-FP6016
•
•
For the third calculation, you will use the new market value of the company, the
number of shares outstanding, and the number of new shares outstanding in the future
(rights offering) to derive the answer.
For the last calculation, you will use the current stock price and ex-rights price to
derive the answer.
3
Excel Examples
EXAMPLE 1:
Discount Rate
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
ANSWER: NPV
EXAMPLE 2:
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
ANSWER
EXAMPLE 3:
Finance Rate
Reinvestment Rate
ANSWER
Beginning in column A, there are 16 examples of how to calculate basic accounting and math equations in Excel. Column C includes annotations for some of the examples.
NET PRESENT VALUE
12%
-65
10
20
40
65
-20
$18.30
Don't include the year 0 cash flow of -$65 (cell B6) because the payments occur at the
BEGINNING of the first period.
INTERNAL RATE OF RETURN (IRR)
-65
10
20
40
65
-20
22.41%
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
Note: Use the yearly data from Example 2.
Discount $100 back 5 years at a 12% discount rate.
(Discount Rate)
(# Periods or years being discounted)
(FV)
$56.74
12%
5
100
$176.23
Compound $100 up 5 years at a 12% discount rate.
(Discount Rate)
(# Periods or years being compounded)
(PV)
EXAMPLE 6:
Present Value =
Future Value =
Discount Rate
ANSWER
FINDING N (NPER) NUMBER OF PERIODS (OR YEARS)
$50
$100
12%
6.12
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 Future Value or Present Value input
negative for the formula to work.
How long would it take to discount $100 down to $25 using a 12% discount rate?
Present Value =
Future Value =
Discount Rate
$100
$25
12%
ANSWER
-12.23
EXAMPLE 7:
Present Value =
Future Value =
N (Nper)
ANSWER
Payment (PMT) =
Future Value =
N (Nper)
ANSWER
EXAMPLE 8:
Present Value =
Future Value =
N (Nper)
Interest Rate
ANSWER
EXAMPLE 9:
SUM
AVERAGE
VARIANCE
STANDARD DEVIATION
CORRELATION
COVARIANCE
FINDING I (INTEREST RATE)
$100
$200
5
14.87%
$100
$750
5
20.40%
Or 12.23 years. Note that years cannot be negative. You have to make either the Future Value
or Present Value 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 or Future Value input 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.
FINDING THE PAYMENT AMOUNT (PMT) OR ANNUITY AMOUNT
$0
$100,000
20
12%
$1,387.88
SUM, AVERAGE, VARIANCE, STANDARD DEVIATION,
CORRELATION, COVARIANCE
0.12
0.15
0.08
0.06
0.08
0.4900
0.0980
0.0013
0.0363
0.3928
0.0012
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 Future Value input to be negative so your answer comes out positive.
0.09
0.11
0.15
0.03
-0.12
EXAMPLE 10:
CALCULATING A BOND'S PRICE
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
1/1/00
1/1/22
0.08
0.09
Redemption
100
Frequency
Basis
Bond Price
2
0
90.49
Multiply by 10
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 of Return)
(Bond's Face Value, Par Value, or Fair Price)
Note that it is $100, not $1,000. You make the adjustments by multiplying the answer by 10.
Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1.
Always leave it blank.
The answer, but you need to multiply it by 10 to get the actual bond price.
Note: Excel gives the bond price in 2 digits like in cell B109. You need to multiply it by 10 to get
the actual bond price.)
(ANSWER = 904.91)
EXAMPLE 11:
CALCULATING A BOND'S 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 makes semiannual payments, what is its yield to maturity?
Settlement
Maturity
Rate
Pr
1/1/00
1/1/22
0.08
96.017
Redemption
100
Frequency
Basis
Yield to Maturity
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 bond's price per $100 face value.
(Bond's Face Value, Par Value, or Fair Price)
Note that it is $100, not $1,000.
Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1.
Always leave it blank.
(ANSWER = 8.40%)
EXAMPLE 12:
CALCULATING THE EFFECTIVE ANNUAL INTEREST RATE
Suppose 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
Effective Annual Interest Rate
5.25%
4
5.3543%
(Number of compounding periods per year)
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.
(ANSWER = 5.35%)
EXAMPLE 13:
CALCULATING THE ANNUAL NOMINAL INTEREST RATE
Suppose 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
Nominal Annual Interest Rate
5.35%
4
5.2459%
(Number of compounding periods per year)
(ANSWER = 5.25%)
CALCULATING THE INTEREST RATE PER PERIOD OF A LOAN
OR AN INVESTMENT
EXAMPLE 14:
If you make monthly payments of $200 on an $8,000 loan over 4 years, what is the Annual Interest Rate of the loan?
4
-200
8000
Years of the Loan
Monthly Payment
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.
(ANSWER = .77%)
Note: Multiply the Monthly Interest Rate by 12 to get the annual rate.
(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
Year 2
Year 3
1.146
1.053
1.176
0.953
7.84%
EXAMPLE 16:
Adding cell B163 to cell B164:
Subtracting cell B163 from cell B164:
Multiplying cell B163 by cell B164:
Dividing cell B164 by cell B163:
Using Parentheses: Multiplying cell B163
by (cell B164 + cell B165):
Calculating cell B163 to the power of cell
B164:
Calculating the Square Root of cell B171:
Calculating the Natural Logarithm of cell
B171:
End of worksheet
SIMPLE MATH CALCULATIONS
2
2
5
4
0
4
1
14
4
2
1.3863
Add 1 to all positive returns.
Add 1 to all positive returns.
Add 1 to all positive returns.
For negative returns, subtract it from 1. You have to do this to keep all data positive.
Note: Place a minus 1 after the formula to get rid of the whole number.
(ANSWER = 7.84%)
Purchase answer to see full
attachment