Number 4

Project 5

On Tab 2, you did not calculate IPMTs. All the related results are changed.

3/2: Early submission.

Cost of Capital Tab:

In Cell L46, Tax Rate (TC) = 34% = 26% (Federal tax rate) + 8% (State tax rate).

Payback Tab:

In Cell E37, you should use WACC calculated from Cost of Capital tab. All related calculations should be changed.

NPV and Projected After-tax Cash Flows should be the same amount of $191.1 for Year 0.

You used the incorrect formulas when calculating NPV2. All related calculations should be changed.

You did not calculate NPV value.

Budget Projections Tab:

Expense increases 2.5% = 0.025. You used the incorrect number in the related calculations.

Feedback Date

Mar 2, 2022 7:51 PM

Feedback for Project 4

Submission Feedback

Overall Feedback
2/18: Tab 2 Annuities Q5, Q6, and Q7: You need to find the PV (which is sum of the PVs) of the cash flows. Tab 2 Annuities Q5, Q6, and Q7: You need to find the PV (which is sum of the PVs) of the cash flows. Tab 3- Capital Budgeting For Table 2-After-tax Cash flow Timeline: you should use the following formula. Projected After-tax Cash Flows (column I) = Projected Cash inflows from Operation (column B) – Projected Cash Outflows from Operation (column C) – Projected Federal Income Taxes (column F) – Projected State Income Taxes (Column G). Remember to correct related questions accordingly.

Project 3

Sheet 2:

You did not correct Cells B7, C7, C14 per the milestone feedback.

On Sheet 4:

Q1: Cell C13 = 139.16X60% per the provided information.

Q2:
Required profit	=	(value in B14)
Existing profit	=	(value in C14)

Q3: Using 100% for Sales in the calculations for Markup % on cost.

On Question 3, the result of 54% is correct for markup % on cost.

My suggested solution for Q4 is:

	Total
Variable Costs	$ 180.00
Fixed Cost	$ 83.50
Total Costs	$ 263.50
Sales	$ 487.77


Units sold	18
Sales Price per unit	$ 27.10

Submission Feedback

Overall Feedback

2/16: Second submission.

Sheet 1:

How did you get 88 in cell B67 and 213 in cell C69?

Again, on Question #2: You should use the formula “Profit % = Profit / Revenue” when calculating Profit % for Total (in cell D70). Do not use ‘sum’ function at here.

You did not correct Sheet 2 and Sheet 3 based on the feedback I posted.

For example, how did you get 8.8 for Contribution Margin if you use the formula: Contribution Margin = Sales – VC.

The same mistakes occurred in Sheet 4.

Please carefully check your calculations.

2/15: Milestone submission.

On Sheet 1:

Table 2 should use Fixed cost per month “$3 million (instead of $10) per month” because Project 2->Tab 2->Question #3 states that “The variable cost per unit is $10 and the fixed costs are $3 million per month.” Please correct the related numbers on Question 2 accordingly.

Question #2: “Marginal Contribution = Revenue – Variable Costs” is the correct formula you should use.

Question #2: You should use the formula “Profit % = Profit / Revenue” when calculating Profit % for Total (in cell D70). Do not use ‘sum’ function at here.

On Sheet 2:
Please do not use “Total Fixed Costs (Millions- from Tab1)” which is inaccurate statement.
For Question #1, you need to carefully read the assumptions which indicate that the Fixed Costs is allocated based on the number of boxes sold. For example, Total FC for Standard Boxes = (Volumes per year for Standard Boxes/Total Volume for both boxes) * Total FX = (108/126) * 156 =133.71. You need to correct all related calculations accordingly.

Sheet 3:

For Question #1, Total Allocated costs (row 14) for Cost of Standard Boxes (Column H) should be the sum of the computed results in column H (=16.84).

Your calculations for the Allocated cost per box are incorrect. For example, for Standard boxes, Allocated Cost per box = Total Allocated costs (16.84) / Number of boxes per year (108) = 0.16. Please correct your related calculations for both standard boxes and Deluxe boxes.

On Sheet 4:
For Question #1, check “Fixed Costs” and “Contribution” numbers if you revised the costs on Sheet 3. They should be consistent.
Revised other related questions caused by the above changes.

Feedback Date

Feb 16, 2022 10:27 PM

Project 1

Submission Feedback

Overall Feedback

1/22: The revision for the workbook is good. No need to resubmit it as a part of the entire project.

1/19: First milestone submission (incomplete submission).

Under the Ratio Analysis tab:

Double check your calculations for Earnings per share. Notice that the unit of the earnings is million. So, you need to time your computed result by 1,000,000. Change the unit of the Price-earning ratio accordingly.

Under the Common-size Analysis tab:

You did not standardize the Income Statement (below the Balance Sheet).

Under the Cash Flow Analysis tab:

Sale of common stock	3	4
Payment of cash dividends	34	88
Purchase of treasury stock	80	15

Feedback Date

Jan 22, 2022 9:52 PM

1/25:

Notice that: The following arguments/conclusions are incorrect/inaccurate.

“LGI seems to be more efficient in terms of sales.”

“Asset’s efficiency can be assessed in terms of the liquidity ratio as well as Return on Assets (ROA). ” Notice that liquidity ratios are not used to assess the assets’ efficiency.

Leverage ratios (not liquidity ratio) should be used in discussing the financial leverage.

Number 4

Instructions

Answer all questions in this workbook. Be sure to read the introductory text on tabs 1 and 3 as well as these instructions.

Keep in mind that the focus of this project is corporate finance. The information generated by the accounting system is important; but in finance, decisions are driven by an analysis of cash flows rather than profits.

Tab 1 contains a series of exercises on the concept of the time value of money. These exercises do not relate directly to the issues facing LGI.

Tab 2 focuses on the concept of annuities. The first few questions do not pertain specifically to LGI; the latter questions do.

Tab 3 pertains to whether LGI should acquire assets that may enhance the company’s productivity and thus improve financial performance.

Tab 1 – TVM

1. Briefly explain the meaning of the term “present value” in your own words.
Present value is the sum of money that must be put in place to ensure that a future goal is attained.
2. Briefly explain the meaning of the term “future value” in your own words.
Future value is future date of an investment at an assumed growth rate
3. What is the future value in five years of $1,500 invested at an interest rate of 4.75%?
$ 1,891.74
4. What is the future value of a single payment with the following characteristics?
PV	$950
NPER	6	years
RATE	5.5%
$ 1,309.90
5. What is the present value of $62,000 in six years, if the relevant interest rate is 8.1%?
$ 38,854.16
6. What is the present value of a single payment with the following characteristics?
NPER	11	years
RATE	7.7%
FV	$10,000
$ 4,422.09
7. The present value of a payment is $4000. The future value of that payment in four years will be $4800. What is the annual rate of return?
4.66%
8. What is the annual rate of return of a single payment with the following characteristics?
PV	$1,000
NPER	12	years
FV	$10,000
21%

Time value of money (TVM) exercises
There are five variables in TVM calculations: present value, number of periods, rate of return, regular payments, and future value. If four of the variables are known, then the fifth can be calculated using algebra, a financial calculator, or a computer program such as Excel.

Excel functions for the five variables are as follows:

 PV—present value
 NPER—number of periods
 RATE—rate of return
 PMT—regular payments
 FV—future value

Tab 2 – Annuities

1. How many years would be required to pay off a loan with the following characteristics?
PV	$11,500
RATE	8.8%
PMT	$1,600	(annual payments)
	11.8688443176
2. What is the annual payment required to pay off a loan with the following characteristics?
PV	$14,700
RATE	9.9%
NPER	10	years
	$ 2,382.09
3. Why is FV not part of the calculations for either question 1 or question 2?
Question 1 & 2 cannot use the future value as the annual payments have been provided in the questions.
4. At what annual rate of interest is a loan with the following characteristics?
NPER	12	years
PMT	$100,000
PV	$1,000,000
	2.92%
For questions 5-8, LGI’s cost of capital is			8.15%
5. LGI projects the following after-tax cash flows from operations from
its aging Bowie, Maryland plant (which first went on line in 1953)
over the next five years. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)	$ 41.61
2	(45)	$ 38.47
3	(45)	$ 35.57
4	(45)	$ 32.89
5	(45)	$ 30.41
		$ 178.96
6. LGI extended the analysis out for an additional 7 years, and generated the
following projections. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)	$ 41.61
2	(45)	$ 38.47
3	(45)	$ 35.57
4	(45)	$ 32.89
5	(45)	$ 30.41
6	(45)	$ 28.12
7	(45)	$ 26.00
8	(45)	$ 24.04
9	(45)	$ 22.23
10	(45)	$ 20.56
11	(45)	$ 19.01
12	(45)	$ 17.57
		$ 336.50
7. The CFO asked the team to undertake a more detailed analysis of the plant’s costs, noting that while
it is convenient for making calculations when projections result in data that can be treated like an annuity,
this does not always represent the most accurate estimate of future results. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)	$ 41.61
2	(50)	$ 42.75
3	(55)	$ 43.48
4	(60)	$ 43.86
5	(70)	$ 47.31
		$ 219.01
8. Because the estimates of the cash flows in Qs5-7 are all negative, LGI is considering selling the Bowie property
and using other existing facilities more efficiently. LGI received four preliminary offers from potential buyers for the Bowie
property. What is the PV of each offer? From a profit maximizing point of view, which offer should LGI accept?
		Present Value
Offer A	$101.9 million, paid today	101.9
Offer B	$19.9 million per year, to be paid over the next 8 years	$ 113.71
Offer C	$201.9 million, to be paid in year 8	107.8758246673
Offer D	$17.9 million per year, to be paid over the next 7 years	$ 120.45
	plus a $51.9 million payment in year 8
	Offer D
9. Define the term annuity in your own words. How might the concept of an annuity impact the process of
capital budgeting and new asset acquisition?
Annuity can be referred to as a fixed number of payments paid to someone each year.
By assessing the annuity, one can know the value of an asset as well as identifying the
value needed to purchase an asset. Moreover, management can know when to purchase
an asset either in the short term or in the long-term.

Tab 2 – Annuities

Tab 3 – Capital Budgeting

			Table 1 – Data
Cost of the new manfactoring equipment (at year=0)			$	191.1	million
Corporate income tax rate – Federal				26.0%
Corporate income tax rate – State of Maryland				8.0%
Discount rate for the project				6.0%
			Table 2 – After-tax Cash Flow Timeline
	(all figures in $ millions)
Year	Projected Cash Inflows from Operations	Projected Cash Outflows from Operations	Depreciation Expense	Projected Taxable Income	Projected Federal Income Taxes	Projected State Income Taxes	Projected After-tax Cash Flows	Present Value
0
1	850.0	840.0	23.9	(13.9)	(3.6)	(1.1)	14.7	$13.89	$14.03
2	900.0	810.0	23.9	66.1	17.2	5.3	67.5	$123.79	$125.64
3	990.0	870.0	23.9	96.1	25.0	7.7	87.3	$233.41	$238.02
4	1,005.0	900.0	23.9	81.1	21.1	6.5	77.4	$268.27	$274.85
5	1,200.0	1,100.0	23.9	76.1	19.8	6.1	74.1	$312.23	$321.35
6	1,300.0	1,150.0	23.9	126.1	32.8	10.1	107.1	$526.75	$544.59
7	1,350.0	1,300.0	23.9	26.1	6.8	2.1	41.1	$229.56	$238.38
8	1,320.0	1,300.0	23.9	(3.9)	(1.0)	(0.3)	21.3	$132.40	$138.09
			Table 3 – Example – Computing Projected After-tax Cash Flows
	For Year 4	(all figures in $ millions)
	Projected Cash Inflows from Operations		1005.0			Projected Cash Inflows from Operations		1005.0
minus	Projected Cash Outflows from Operations		(900.0)		minus	Projected Cash Outflows from Operations		(900.0)
minus	Depreciation Expense		(23.9)		minus	Depreciation Exp – (Depreciation is not a cash flow)		0.0
equals	Projected Taxable Income		81.1		minus	Projected Federal Income Taxes		(21.1)
					equals	Projected State Income Taxes		(6.5)
	Projected Taxable Income		81.1			Projected After-tax Cash Flows		77.4
times	Corporate income tax rate – Federal		26.0%
equals	Projected Federal Income Taxes		21.1
	Projected Taxable Income		81.1
times	Corporate income tax rate – State		8.0%
equals	Projected State Income Taxes		6.5
1. Complete Table 2. Compute the projected after tax cash flows for each of years 1-8.
2. Compute the total present value (PV) of the projected after tax cash flows for years 1-8.
	$ 1,894.95
3. Compute the net present value (NPV) of the projected after tax cash flows for years 0-8.
	1,649.21
4. Compute the internal rate of return (IRR) of the project.
	ERROR:#NUM!
5. The CFO believes that it is possible that the next few years will bring a very low interest rate environment.
Therefore, she has asked that you repeat the NPV calculation in question 3 showing the case where the
discount rate for the project is		4.95%
	npv	$ 1,703.95

Technologically advanced manufacturing equipment proposal
LGI has decided to divest itself of some unproductive factory assets. The vice president of production is proposing to acquire robotics-based manufacturing equipment to enable more cost-effective production.

The CFO has asked you to evaluate the cash flow projections associated with the equipment purchase proposal and recommend whether the purchase should go forward. Table 2 shows projections of the cash inflows and outflows that would occur during the first eight years using the new equipment.

Keep the following in mind:

 Depreciation. The equipment will be depreciated using the straight-line method over eight years. The projected salvage value is $0.
 Taxes. The CFO estimates that company operations as a whole will be profitable on an ongoing basis. As a result, any accounting loss on this specific project will provide a tax benefit in the year of the loss.

Number 4

Instructions

Answer all questions in this workbook. Be sure to read the introductory text on tabs 1 and 3 as well as these instructions.

Tab 1 contains a series of exercises on the concept of the time value of money. These exercises do not relate directly to the issues facing LGI.

Tab 2 focuses on the concept of annuities. The first few questions do not pertain specifically to LGI; the latter questions do.

Tab 3 pertains to whether LGI should acquire assets that may enhance the company’s productivity and thus improve financial performance.

Tab 1 – TVM

1. Briefly explain the meaning of the term “present value” in your own words.
2. Briefly explain the meaning of the term “future value” in your own words.
3. What is the future value in five years of $1,500 invested at an interest rate of 4.75%?
4. What is the future value of a single payment with the following characteristics?
PV	$950
NPER	6	years
RATE	5%
5. What is the present value of $62,000 in six years, if the relevant interest rate is 8.1%?
6. What is the present value of a single payment with the following characteristics?
NPER	11	years
RATE	7%
FV	$10,000
7. The present value of a payment is $4000. The future value of that payment in four years will be $4800. What is the annual rate of return?
8. What is the annual rate of return of a single payment with the following characteristics?
PV	$1,000
NPER	12	years
FV	$10,000

Tab 2 – Annuities

1. How many years would be required to pay off a loan with the following characteristics?
PV	$11,500
RATE	8%
PMT	$1,600	(annual payments)
2. What is the annual payment required to pay off a loan with the following characteristics?
PV	$14,700
RATE	9%
NPER	10	years
3. Why is FV not part of the calculations for either question 1 or question 2?
4. At what annual rate of interest is a loan with the following characteristics?
NPER	12	years
PMT	$100,000
PV	$1,000,000
For questions 5-8, LGI’s cost of capital is			8.05%
5. LGI projects the following after-tax cash flows from operations from
its aging Bowie, Maryland plant (which first went on line in 1953)
over the next five years. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)
2	(45)
3	(45)
4	(45)
5	(45)
6. LGI extended the analysis out for an additional 7 years, and generated the
following projections. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)
2	(45)
3	(45)
4	(45)
5	(45)
6	(45)
7	(45)
8	(45)
9	(45)
10	(45)
11	(45)
12	(45)
7. The CFO asked the team to undertake a more detailed analysis of the plant’s costs, noting that while
it is convenient for making calculations when projections result in data that can be treated like an annuity,
this does not always represent the most accurate estimate of future results. What is the PV of these cash flows?
	Projected after-tax cash flows
Year	(in $ millions)
1	(45)
2	(50)
3	(55)
4	(60)
5	(70)
8. LGI received four preliminary offers from potential buyers interested in acquiring
the Bowie factory. What is the PV of each offer? Which offer should LGI accept?
Offer A	$101 million, paid today
Offer B	$20 million per year, to be paid over the next 8 years
Offer C	$201 million, to be paid in year 8
Offer D	$18 million per year, to be paid over the next 7 years
	plus a $50 million payment in year 8

Tab 2 – Annuities

Tab 3 – Capital Budgeting

			Table 1 – Data
Cost of the new manfactoring equipment (at year=0)			$	191.1	million
Corporate income tax rate – Federal				21.0%
Corporate income tax rate – State of Maryland				8.0%
Discount rate for the project				6.0%
			Table 2 – After-tax Cash Flow Timeline
	(all figures in $ millions)
Year	Projected Cash Inflows from Operations	Projected Cash Outflows from Operations	Depreciation Expense	Projected Taxable Income	Projected Federal Income Taxes	Projected State Income Taxes	Projected After-tax Cash Flows
0
1	850.0	840.0
2	900.0	810.0
3	990.0	870.0
4	1,005.0	900.0
5	1,200.0	1,100.0
6	1,300.0	1,150.0
7	1,350.0	1,300.0
8	1,320.0	1,300.0
			Table 3 – Example – Computing Projected After-tax Cash Flows
	For Year 4	(all figures in $ millions)
	Projected Cash Inflows from Operations		1005.0			Projected Cash Inflows from Operations		1005.0
minus	Projected Cash Outflows from Operations		(900.0)		minus	Projected Cash Outflows from Operations		(900.0)
minus	Depreciation Expense		(23.9)		minus	Depreciation Exp – (Depreciation is not a cash flow)		0.0
equals	Projected Taxable Income		81.1		minus	Projected Federal Income Taxes		(17.0)
					equals	Projected State Income Taxes		(6.5)
	Projected Taxable Income		81.1			Projected After-tax Cash Flows		81.5
times	Corporate income tax rate – Federal		21.0%
equals	Projected Federal Income Taxes		17.0
	Projected Taxable Income		81.1
times	Corporate income tax rate – State		8.0%
equals	Projected State Income Taxes		6.5
1. Complete Table 2. Compute the projected after tax cash flows for each of years 1-8.
2. Compute the total present value (PV) of the projected after tax cash flows for years 1-8.
3. Compute the net present value (NPV) of the projected after tax cash flows for years 0-8.
4. Compute the internal rate of return (IRR) of the project.
5. The CFO believes that it is possible that the next few years will bring a very low interest rate environment.
Therefore, she has asked that you repeat the NPV calculation in question 3 showing the case where the
discount rate for the project is		4.95%

Number 4

UMGC
MBA 620: Financial

Decision Making

Project 4: Review and

Practice Guide

Project 4: Review and

Practice Guide

Time Value of Money and Annuities

Contents
Topic 1: Time Value of Money ………………………………………………………………………………………………………. 3

Outline …………………………………………………………………………………………………………………………………… 3

Time Value of Money (TVM) ……………………………………………………………………………………………………… 3

Using a Timeline ………………………………………………………………………………………………………………………. 3

Terminology ……………………………………………………………………………………………………………………………. 4

Compounding-Terminology ………………………………………………………………………………………………………. 4

Future Value Equation ……………………………………………………………………………………………………………… 4

Compounding Interest ……………………………………………………………………………………………………………… 5

Compound Interest ………………………………………………………………………………………………………………. 5

Compounding More Than Once a Year ……………………………………………………………………………………….. 5

Effect of Compounding Frequency …………………………………………………………………………………………….. 6

Using Excel ……………………………………………………………………………………………………………………………… 6

Excel Functions: FV…………………………………………………………………………………………………………………… 6

Continuous Compounding ………………………………………………………………………………………………………… 7

Discounting-Terminology ………………………………………………………………………………………………………….. 7

Present Value and Discounting ………………………………………………………………………………………………….. 7

Excel Functions: PV and NPV …………………………………………………………………………………………………….. 8

Finding the Interest Rate ………………………………………………………………………………………………………….. 8

Excel Functions: RATE ………………………………………………………………………………………………………………. 9

Finding the Number of Periods ………………………………………………………………………………………………….. 9

Summary of Key Concepts ………………………………………………………………………………………………………… 9

Topic 2: Annuity & Perpetuity ……………………………………………………………………………………………………… 10

Outline …………………………………………………………………………………………………………………………………. 10

The Analysis of Multiple Cashflows ………………………………………………………………………………………….. 10

Future Value of Multiple Cash Flows …………………………………………………………………………………….. 10

Present Value of Multiple Cash Flows …………………………………………………………………………………… 10

Level Cash Flows: Annuities and Perpetuities …………………………………………………………………………. 10

Annuities ………………………………………………………………………………………………………………………………. 11

Types of Annuities ………………………………………………………………………………………………………………….. 11

Present Value of an Annuity ……………………………………………………………………………………………………. 11

Annual Payment of an Annuity ………………………………………………………………………………………………… 11

Excel Functions: PMT ……………………………………………………………………………………………………………… 12

Interest Rate of an Annuity ……………………………………………………………………………………………………… 12

Perpetuity …………………………………………………………………………………………………………………………….. 12

APR and EAR: Two Ways of Quoting Interest Rates ……………………………………………………………………. 12

Annual Percentage Rate (APR) …………………………………………………………………………………………………. 13

Effective Annual Interest Rate (EAR) ………………………………………………………………………………………… 13

Problems/Exercises ……………………………………………………………………………………………………………………. 14

What to do ……………………………………………………………………………………………………………………………. 14

Self Study Problems ……………………………………………………………………………………………………………….. 14

Questions and Problems …………………………………………………………………………………………………………. 14

Exercise: 6.40 ………………………………………………………………………………………………………………………… 14

Solution: 6.40 ………………………………………………………………………………………………………………………… 15

Exercise: 6.41 ………………………………………………………………………………………………………………………… 15

Solution: 6.41 ………………………………………………………………………………………………………………………… 16

Exercise 6.42: Notes on the Solution ………………………………………………………………………………………… 16

Solution: 6.42 ………………………………………………………………………………………………………………………… 17

References ……………………………………………………………………………………………………………………………. 17

Project 4 Review and Practice Guide

Back to Table of Contents

Topic 1: Time Value of Money

Outline
• What is time value of money?

• Terminology

• Compounding

• Discounting

Time Value of Money (TVM)
• Consume today or tomorrow?

o TVM is based on the belief that people prefer to consume goods today rather than wait to

consume the same goods tomorrow (Parrino et al., 2012).

▪ An apple today is more valuable to us than an apple we can have in one year.

▪ Money has a time value because buying an apple today is more important than buying

an apple in one year.

• For financial managers

o Determine today’s value of a future cash flow, or a series of cash flows, mostly in financing

or investment decisions (Parrino et al., 2012).

Time Value of Money Explained

Using a Timeline

Adapted from Parrino et al. (2012)

https://www.investopedia.com/terms/t/timevalueofmoney.asp

Project 4 Review and Practice Guide

Back to Table of Contents

Terminology

Compounding-Terminology

Future Value Equation
Equation to find a future value

FV𝑛 = PV × (1 + i)
𝑛

FVn = future value of investment at end of period n

PV = original principal (P0) or present value

i = the rate of interest per period

n = the number of periods, often in years

Project 4 Review and Practice Guide

Back to Table of Contents

Compounding Interest

Compound Interest
How to Compound Interest: Growth of $1,000 at 10%

The amount of simple interest earned on $1,000 invested at 10% remains constant at $100 per year, but

the amount of interest earned on interest increases each year as more and more interest builds.

Compounding accelerates the growth of the total interest earned.

Based on an image from Parrino et al. (2012)

Compounding More Than Once a Year
o The more frequently interest is compounded, the larger the future value of $1 at the end of

a given time period

o If compounding occurs m times within a period, the future value equation becomes

o 𝐹𝑉𝑛 = 𝑃𝑉 × (1 + 𝑖 𝑚⁄ )
𝑚𝑛

Project 4 Review and Practice Guide

Back to Table of Contents

Effect of Compounding Frequency
Future value of $100 after 10

years, by compounding frequency

Daily: $271.79

Monthly: $270.70

Quarterly: $268.51

Yearly: $259.37

Using Excel
• Enter the formulas

• Use the built-in functions

o Link to the Excel functions by category

o https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-

9bd1-63f26a86c0eb

Excel Functions: FV
FV(rate,nper,pmt,[pv],[type]) calculates the future value of an investment based on a constant interest

rate (Microsoft, n.d.).

Rate Required. The interest rate per period.

Nper Required. The total number of payment periods in an annuity.

Pmt Required. The payment made each period; it cannot change over the life of the annuity.

Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you

must include the pv argument.

Pv Optional. The present value, or the lump-sum amount that a series of future payments

is worth now. If pv is omitted, it is assumed to be 0, and you must include the pmt argument.

Type Optional. Indicates when payments are due—0 for end of period or 1 for beginning of

period. If type is omitted, it is assumed to be 0.

https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

Project 4 Review and Practice Guide

Back to Table of Contents

Continuous Compounding
• Continuous Compounding

o When compounding occurs on a continuous basis, the future value equation becomes

e = 2.71828, the base of the natural logarithm

Discounting-Terminology

Present Value and Discounting
• Present Value Equation

o General equation to find present value

o (1 + i)n is the present value factor or discount factor.

o The interest rate i is the discount rate.

• Present Value Concepts

o Time and the discount rate affect present value:

▪ The greater the time before a cash flow is to occur, the smaller the present value of the

cash flow.

▪ The higher the discount rate, the smaller the present value of a future cash flow (Parrino

et al., 2012).

𝐹𝑉𝑛 = 𝑃 × 𝑒𝑖 × 𝑛

PV =
FV𝑛

(1 + i)𝑛

Project 4 Review and Practice Guide

Back to Table of Contents

Excel Functions: PV and NPV
PV(rate,nper,pmt,[fv],[type]) calculates the present value of a loan or an investment, based on a

constant interest rate (Microsoft, n.d.).

Rate Required. The interest rate per period.

Nper Required. The total number of payment periods in an annuity.

Pmt Required. The payment made each period; it cannot change over the life of the annuity.

Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you

must include the pv argument.

Fv Optional. The future value or a cash balance you want to attain after the last payment is

made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is

omitted, you must include the pmt argument.

Type Optional. Indicates when payments are due, 0 for end of period or 1 for beginning of

period. If type is omitted, it is assumed to be 0.

NPV( rate,value1,[value2],…) calculates the net present value of an investment by using a discount rate

and a series of future payments (negative values) and income (positive values) (Microsoft, n.d.).

Rate Required. The interest rate per period.

Value1, Value2 Value1 is required; other values are optional.

1 to 254 Arguments representing the payments and income.

Value1, Value2 Must be equally spaced in time and occur at the end of each period.

NPV uses the order of Value1, Value2 to interpret the order of cash flows. Be sure to enter
your payment and income values in the correct sequence.

Arguments that are empty cells, logical values, or text representations of numbers, error values,

or text that cannot be translated into numbers are ignored.

If an argument is an array or reference, only numbers in that array or reference are counted.

Empty cells, logical values, text, or error values in the array or reference are ignored.

Finding the Interest Rate
• Many situations require a time value of money calculation to determine a rate of change or

growth rate (Parrino et al., 2012)

• An investor or analyst may want to know

o growth rate in sales

o rate of return on an investment

o effective interest rate on a loan

How to solve for i

FV/PV = (1 + i)n

(FV/PV)1/n = (1 + i)

i = (FV/PV)1/n – 1

FV𝑛 PV × (1 + i )
𝑛

Project 4 Review and Practice Guide

Back to Table of Contents

Excel Functions: RATE
RATE(nper, pmt, pv, [fv], [type], [guess]) returns the interest rate per period of an annuity (Microsoft,

n.d.).

Nper Required. The total number of payment periods in an annuity.

Pmt Required. The payment made each period. It cannot change over the life of the annuity.

Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you

must include the fv argument.

Pv Required. The present value—the total amount that a series of future payments is

worth now.

Fv Optional. The future value, or a cash balance you want to attain after the last payment is

made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is

omitted, you must include the pmt argument.

Type Optional. Indicates when payments are due, number 0 for end of period or 1 for

beginning of period. If type is omitted, it is assumed to be 0.

Guess Optional. Your guess for what the rate will be. If you omit guess, it is assumed to be 10

percent. If RATE does not converge, try different values for guess. RATE usually converges if

guess is between 0 and 1.

Finding the Number of Periods

Solve for n using the following equations (“l,” as in “ln” below, stands for logarithm):

FV/PV = (1 + i)n

ln(FV/PV) = n × ln(1 + i)

n = ln(FV/PV) / ln(1+ i)

Or, PDURATION (rate, pv, fv)

Rate Required. Rate is the interest rate per period.

Pv Required. Pv is the present value of the investment.

Fv Required. Fv is the desired future value of the investment.

Summary of Key Concepts
1. What is time value of money and why is it important?

2. What do future value, principal amount, simple interest and compound interest mean? How is

the future value formula used to make business decisions? Provide an example.

3. Explain present value and how it relates to future value. How is the present value formula used

to make business decisions? Provide an example.

FV𝑛 = PV × (1 + i)
𝑛

Project 4 Review and Practice Guide

Back to Table of Contents

Topic 2: Annuity & Perpetuity

Outline
• The Analysis of Multiple Cashflows

• Annuity

• Perpetuity

• APR and EAR

The Analysis of Multiple Cashflows

Future Value of Multiple Cash Flows

Based on an image from Parrino et al. (2012)

Present Value of Multiple Cash Flows

Based on an image from Parrino et al. (2012)

Level Cash Flows: Annuities and Perpetuities

• Annuity

o A series of equally-spaced and level cash flows extending over a finite number of periods

• Perpetuity

o A series of equally-spaced and level cash flows that continue forever

Project 4 Review and Practice Guide

Back to Table of Contents

Annuities

Types of Annuities
• Ordinary Annuity

o Cash flows occur at the end of a period

▪ mortgage payment

▪ interest payment to bondholder

• Annuity Due

o Cash flows occur at the beginning of a period

▪ lease

Present Value of an Annuity
Example

• amount needed to produce the annuity

• current fair value or market price of the annuity

• amount of a loan that can be repaid with the annuity

Annual Payment of an Annuity
Calculate the annual payment of an annuity

PVA = [CF ×
1

1 + 𝑖
] + [CF ×

(1 + 𝑖)2
] + ⋯ + [CF ×

(1 + 𝑖)𝑛
]

=
CF

𝑖
× [1 −

(1 + 𝑖)𝑛
]

= CF ×
1 − 1/(1 + 𝑖)𝑛

𝑖

PVA = CF ×
1 −

1
(1 + 𝑖)𝑛

𝑖

CF = PVA
1 −

1
(1 + 𝑖)𝑛

𝑖
⁄

= PVA ×
𝑖

1 − 1/(1 + 𝑖)𝑛

Project 4 Review and Practice Guide

Back to Table of Contents

Excel Functions: PMT
PMT(rate, nper, pv, [fv], [type])

rate Required. The interest rate for the loan.

nper Required. The total number of payments for the loan.

pv Required. The present value, or the total amount that a series of future payments is

worth now; also known as the principal.

fv Optional. The future value, or a cash balance you want to attain after the last payment is

made. If fv is omitted, it is assumed to be 0, meaning the future value is 0.

type Optional. The number 0 or 1 indicates when payments are due.

0 or omitted—Payments are due at the end of the period.

1—Payments are due at the beginning of the period.

Interest Rate of an Annuity
Find out the interest rate of an annuity:

Cannot solve for i analytically,

Use Excel:

RATE(nper, pmt, pv, [fv], [type], [guess])

Perpetuity
A stream of equal cash flows that goes on forever

Preferred stock and some bonds are perpetuities

APR and EAR: Two Ways of Quoting Interest Rates
o The most common way to quote interest rates is in terms of annual percentage rate (APR),

which does not incorporate the effects of compounding

o The most appropriate way to quote interest rates is in terms of effective annual rate (EAR),

which incorporates the effects of compounding (Parrino et al., 2012)

PVA = CF ×
1 − 1/(1 + 𝑖)𝑛

𝑖

𝑃𝑉𝑃0 = 𝐶𝐹 × [
1 −

1
(1 + 𝑖)∞

𝑖
] = 𝐶𝐹 ×

(1 − 0)

𝑖

=
𝐶𝐹

𝑖

Project 4 Review and Practice Guide

Back to Table of Contents

Annual Percentage Rate (APR)
• APR = periodic rate × m

m—the number of periods in a year

• Does not account for the number of compounding periods or adjust the annualized interest rate

for the time value of money

• Not an exact measurement of borrowing/investing rates

Effective Annual Interest Rate (EAR)
• EAR accounts for the number of compounding periods and adjusts the annualized interest rate

for the time value of money

• EAR is a more accurate measure of the rates involved in lending and investing (Parrino et al.,

2012)

Project 4 Review and Practice Guide

Back to Table of Contents

Problems/Exercises

What to do
Complete the practice exercises from the book and the custom exercise that follows in this guide to

gain the knowledge and skills needed to complete the final Project 4 deliverable.

The answers are provided, so you can check your work.

Self Study Problems
• Chapter 5 Self Study problems (all)

• Chapter 6 Self Study problems (all)

Questions and Problems
• Chapter 6

o 6.40

o 6.41

o 6.42

Exercise: 6.40
Trevor Diaz wants to purchase a Tesla. The total cost is $129, 482. Trevor plans to put down $20,000 and

pay the rest using a 5.75% five-year bank loan. What is the monthly payment on this auto loan?

o Cost of new car = $129,482

o Down payment = $20,000

o Loan amount = $129,482 − $20,000 = $109,482

o Interest rate on loan, or i = 5.75%

o Term of loan, or n = 5 years

o Frequency of payment, or m = 12

o Monthly payment on loan = PMT

Project 4 Review and Practice Guide

Back to Table of Contents

Solution: 6.40

Financial Calculator Solution

60 5.75/12 109,482 0 0

N i PV PMT FV

2,103.89

Exercise: 6.41
The Sundarams are buying a new 3,500 sq. ft. house in Kentucky and will borrow $237,000 from a bank

at a rate of 6.375% for 15 years. What will their monthly mortgage be?

o Home loan amount = $237,000

o Interest rate on loan , or i = 6.375%

o Term of loan, or n = 15 years

o Frequency of payment, or m = 12

o Monthly payment on loan = PMT

12 5

1
1

(1 )
PVA PMT

$109, 482 $109, 482
PMT

1 52.0379
1

0575
1

0.0575



−
+

= 

= =

−
 
+ 

 

$2, 103.89

Project 4 Review and Practice Guide

Back to Table of Contents

Solution: 6.41

Financial Calculator Solution

60 6.375/12 -237,000

N i PV PMT FV

2,048.27

Exercise 6.42: Notes on the Solution
If the student is 21, then the first withdrawal will be 40,000(1+.05)45 = $359,400.

Withdrawals grow at 5% per year thereafter (a spreadsheet is helpful here).

Next, find the NPV of the anticipated retirement withdrawals at each anticipated interest rate. This is

the lump sum needed at retirement. Use a spreadsheet or the formula for the present value of a

growing annuity (see next slide) to find the present value of withdrawals:

Project 4 Review and Practice Guide

Back to Table of Contents

Solution: 6.42

𝑃𝑉𝐴𝑛 =
359,400.312

. 1 − .05
𝑥 [1 − (

1.05

1.1
)
20

] = 4,353,086.79

Finally, find the annuity payment that will generate the lump sum at the end of the student’s working

life. Assume 40 years of saving, 35 years of saving, and 30 years of saving.

Retirement Analysis Summary

Investment Age = 25 Investment Age = 30 Investment Age = 35

Rate of

return 8% 10% 15% 8% 10% 15% 8% 10% 15%

Inflation

rate
5%

Retirement

income

level

$40,000

Lump sum

needed at

age 65

$5,160,266

$4,353,087

$3,011,353

$5,160,266

$4,353,087

$3,011,353

$5,160,266

$4,353,087

$3,011,353

Annuity

payment

needed

$19,919

$9,835

$1,693

$29,946

$16,062

$3,417

$45,552

$26,463

$6,927

References
Microsoft. (n.d.). Excel functions (by category). Retrieved July 22, 2021, from

https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-

63f26a86c0eb

Parrino, R., Kidwell, D. S., & Bates, T. W. (2012). Fundamentals of corporate finance. Wiley.

Back to Table of Contents

Now that you have read this Review and Practice Guide and completed
the exercises, you are ready to participate in the assignment in Step 3.

Number 4

Project 4 Questions – Template

Instructions: Answer the five questions below. Base your analysis only on the Excel workbook.

Provide a detailed response below each question. Use 12-point font and double spacing. Maintain the existing margins in this document. Your final Word document, including the questions, should not exceed 5 pages. Include a title page in addition to the five pages. Any tables and graphs you choose to include are also excluded from the five-page limit. Name your document as follows: P4_Final_lastname_Report_date.

Title Page

Name

Course and section number

Faculty name

Submission date

1. Present-value calculations, rather than future-value calculations, are the key to analysis in the field of corporate finance. Why is this the case? Explain the importance for Largo Global Inc. (LGI) of understanding today’s value of projected future revenues and/or costs.

[insert your answer here]

2. Based on your calculations in Tab 2, Question 8, which offer should LGI accept for the Bowie plant? Explain why. Be sure to include the concepts of risk and potential return as part of your discussion.

[insert your answer here]

3. The proposed sale of the Bowie plant is part of the effort to divest the company of underperforming assets. A total of $1.2 billion in assets, with a book value of $650 million, have been identified for potential sale. Assuming that all these sales could be accomplished in 2021, identify the major impacts on the following:

a. Balance Sheet, especially these accounts:

· Property, plant, and equipment

· Accumulated depreciation

· Net property, plant, and equipment

b. Statement of Cash Flows, especially the Long-Term Investing Activities

c. Income Statement

Explain the potential impacts, both positive and negative, of these changes for LGI.

[insert your answer here]

4. Based on your calculations in Tab 3, Questions 1–4, should LGI proceed with the acquisition of the robotics-based manufacturing equipment? Explain your reasoning. How would the acquisition fit into the efforts to turn the company around?

[insert your answer here]

5. In Tab 3, Question 5, did the change in the discount rate make proceeding with the purchase more or less desirable? What do you conclude from this result? Discuss the role of discount rates in LGI’s decision-making processes.

[insert your answer here]

Number 4

Project 4: Time Value of Money and Annuities
Start Here

Print Project

Transcript

Scenario

Your advice has helped Largo Global Inc. (LGI) make substantive progress. Now, you must provide the means to achieve this increase in efficiency and productivity. You must recommend a plan for making the strategic investments that will keep LGI moving on a sustainable path.

Your Project 4 business will focus on which assets to invest in or whether to disinvest. You will identify the types of assets LGI should eventually acquire and/or renew. You will also determine the investments that will help LGI improve its operations and generate the cash flow needed to improve its bottom line.

Competencies

Your work will be evaluated using the competencies listed below.

· 3.1: Identify numerical or mathematical information that is relevant in a problem or situation.

· 3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.

· 3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.

· 8.1: Evaluate major business/organizational systems and processes and make recommendations for improvement.

Project 4: Time Value of Money and Annuities
Step 1: Prepare for the Project

For the next two weeks, you will focus on an investment strategy that will increase the efficiency and productivity of LGI to keep the company moving in the right direction. You will pay attention to which assets to invest in and divest from to generate cash flow and improve the bottom line.
Log into O’Reilly by following these instructions
and complete the required reading.

Required Reading

Parrino, R. Kidwell, D. S., & Bates, T. W. (2012).
Fundamentals of corporate finance
. Wiley.

Chapter 5: The Time Value of Money

· Section 5.1 to 5.4

Chapter 6: Discounted Cash Flows and Valuation

· Sections 6.1 to 6.4

Recommended Reading

Davis, C. E. & Davis, E. (2011).
Managerial Accounting
. Wiley.
Chapter 9, Capital Budgeting

Project 4: Time Value of Money and Annuities
Step 2: Review and Practice

Using the Project 4 Review and Practice Guide, review investment techniques to set LGI on a path toward a sustainable future. Then apply what you have learned by completing the exercises and problems referenced in the Project 4 Review and Practice Guide.

You must review the guide and do the practice exercises and problems so that you

· are prepared to have informed discussions with your team about LGI’s investment strategy,

· understand which assets LGI should acquire and/or renew, and

· can use this information to make recommendations for improvements.

Complete this review and practice by the end of Week 7.

Review and Practice

You must complete the review and practice content to participate in the discussion in Step 3.

Project 4 Review and Practice Guide

Project 4: Time Value of Money and Annuities
Step 3: Participate in the Required Project 4 Discussion

AzmanL / E+ / Getty Images

You have finished reviewing the material and performing the exercises, but you have some questions. Participate in the Project 4 class discussion. Respond to the two questions below by posting in the discussion; then, respond to two of your classmates’ discussion posts by the end of the week.

Discussion

Discuss the concepts that were most challenging for you in the readings and review material. How did the practice exercises help clarify these?

What did you learn that will help you determine which assets to invest in and where to disinvest to help LGI improve its operations, generate cash flow, and improve the bottom line?

Before you participate in the discussion activity, see
MBA discussion guidelines
.

Course Resource

MBA Discussion Guidelines

Throughout the MBA program, you will be asked to participate in discussions. Assigned discussions, both individual and group work, are part of the process of developing your project deliverables.

In general, address your discussion posts to your classmates, rather than the instructor. Do not attach files; use only the discussion textbox. Your posts do not need the structure or format of formal business memos or reports. These discussions should be an informal exchange of ideas with your peers. You should, of course, still adhere to the norms of standard written English.

To receive the maximum benefit, you should participate in accordance with the guidelines provided below.

· timeliness

· initial posting(s) submitted by 11:59 PM ET on Saturday

· response(s) to other discussion postings submitted by 11:59 PM ET on Tuesday

· proper citation

· cite sources any time you quote or paraphrase an idea or evidence from another work

· use APA citation style (example below)

· meaningful engagement

· posts contribute to substantive scholarly discussion

· student demonstrates professionalism in interaction with peers

· posts critically discuss topics presented in the current week and, when appropriate, in previous weeks

· posts are grounded in the theories and concepts presented in the course

Project 4: Time Value of Money and Annuities
Step 4: Complete the Analysis Calculation for Project 4

Your team has provided you with an Excel workbook containing LGI’s financials. You will use the
Project 4 Excel workbook
to perform discounting of cash flow and valuation calculations.

Complete the analysis calculation for the project:

· Download the
Project 4 Excel Workbook
, click the Instructions tab, and read the instructions.

· Calculate and evaluate the investment choices using the worksheets.

· If you would like instructor feedback on this step, submit your Excel file to the Assignments folder as a milestone by the end of Week 7. This is optional. If you choose to submit the milestone, you will receive instructor feedback you may use to make corrections before submitting your final Project 4. To distinguish the milestone submission from the file you will submit in Step 5, label your file as follows: P4_milestone_lastname_Calculation_date

Project 4: Time Value of Money and Annuities
Step 5: Prepare the Analysis for Project 4

You have developed an in-depth understanding of LGI’s operating efficiency as it relates to costing and its impact on the bottom line. You feel confident that your investment choices will positively boost LGI’s productivity and improve the company’s operations. LGI will finally be on a path of a sustainable future. Answer the questions in the Project 4 Questions – Template document. Prepare your analysis including recommendations for how the company can improve its financial situation.

Complete the analysis report:

Download the Project 4 Questions – Template

· Read the instructions.

· Answer all the questions.

· Include your recommendations.

· Submit the analysis (Word document) and analysis calculation (Excel file) to the Assignments folder as your final deliverable at the end of Week 8. Label your files as follows:

· P4_Final_lastname_Report_date.docx

· P4_Final_lastname_Calculation_date.xlsx

Check Your Evaluation Criteria

Before you submit your assignment, review the competencies below, which your instructor will use to evaluate your work. A good practice would be to use each competency as a self-check to confirm you have incorporated all of them. To view the complete grading rubric, click My Tools, select Assignments from the drop-down menu, and then click the project title.

· 3.1: Identify numerical or mathematical information that is relevant in a problem or situation.

· 3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.

· 3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.

· 8.1: Evaluate major business/organizational systems and processes and make recommendations for improvement.