3 assignments

profileusa94
WorkshopSixPracticeProblems.xlsx
Home>Business & Finance homework help>Accounting homework help>3 assignments

Example 1

Workshop Six Practice Exercises
Example 1: The PV Function
a) What is the present value of $11,000 that is going to be received in 11 years, if the appropriate interest rate is 11 percent?
b) What is the present value of $10,000 received every year for 8 years, if the going rate of interest is 12%?
c) An new piece of equipment returns $7,000 in savings every year for 15 years. It will then be sold (salvaged) for $15,000. What is the value of this equipment today, if the company's required return is 9%?
d) An entrepreneur borrows money to purchase a franchise. Their monthly payment on the loan is $3500, and it lasts 5 years. The loan's annual interest rate (APR) is 8.5%. How much did they borrow?
e) A young couple is interested in purchasing a home. They determine the largest mortgage payment they can afford is $1,500 per month. The current mortgage rate is 3.625%. What is the maximum amount they can borrow with a standard 30-year mortgage. What if they instead choose to do a 15-year mortgage?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 1 Solution

Workshop Six Practice Exercises
Example 1: The PV Function
a) What is the present value of $11,000 that is going to be received in 11 years, if the appropriate interest rate is 11 percent?
b) What is the present value of $10,000 received every year for 8 years, if the going rate of interest is 12%?
c) An new piece of equipment returns $7,000 in savings every year for 15 years. It will then be sold (salvaged) for $15,000. What is the value of this equipment today, if the company's required return is 9%?
d) An entrepreneur borrows money to purchase a franchise. Their monthly payment on the loan is $3500, and it lasts 5 years. The loan's annual interest rate (APR) is 8.5%. How much did they borrow?
e) A young couple is interested in purchasing a home. They determine the largest mortgage payment they can afford is $1,500 per month. The current mortgage rate is 3.625%. What is the maximum amount they can borrow with a standard 30-year mortgage. What if they instead choose to do a 15-year mortgage?
Check below for a detailed solution to this problem.
Input / Output area: This example is designed to give you practice using Excel's PV function.
This function can be used to calculate the Present Value of a single lump sum, of a level series of payments (an annuity), or a combination of the two.
Present Value Interest Rate Number of Periods Payments Future Value Examine the formulas in column C closely…
a) $ 3,490.12 11% 11 $ - $ 11,000.00 Note there is only one lump sum future value in this case, so the annuity payment "pmt" is set to zero.
b) $ 49,676.40 12% 8 $ 10,000.00 $ - In this case there is a regular, repeating payment (an annuity), but no final future value payment.
c) $ 60,542.89 9% 15 $ 7,000.00 $ 15,000.00 This is a combination, with both annuity payments and a final future value.
d) $ 170,594.14 8.5% 5 $ 3,500.00 $ - Note the adjustments in the formula here for monthly periods. The rate is divided by 12 (months per year) and the number of periods is multiplied by 12. Another approach would be to use a monthly rate and the number of months as the periods.
e) $ 328,910.36 3.625% 30 $ 1,500.00 $ - Note again the adjustments for monthly payments. Also, note the future value of a loan is always zero (we are going to pay it off!).
$ 208,033.78 3.625% 15 $ 1,500.00 $ - Go with a 15-year mortgage if at all possible! It is one of the best pieces of financial advice available.
The basic syntax is: =PV(rate,nper,pmt,[fv],[type])
Where:
rate = The discount / interest rate to be applied
nper = The number of periods
pmt = The regular, level, repeating payment in each period (the annuity payments)
fv = The future value (a single lump sum at the end of periods) - This is optional. If not used, the function assumes it is zero.
type = Determines if the calculations are based on payments occurring at the beginning (an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
Optional and generally not used. If not used, the function assumes a zero value and an ordinary annuity (which is generally the case).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 2

Workshop Six Practice Exercises
Example 2: The FV Function
a) What is the future value of $11,000 that is going to be received today, if the appropriate interest rate is 11 percent?
b) What is the future value of $10,000 received every year for 8 years, if the going rate of interest is 12%?
c) You currently have $50,000 in retirement savings. You plan to add $7,000 every year until you retire in 20 years. If you earn 9% annually, how much will you have at retirement?
d) You take out a 30-year mortgage on a new home for $350,000. Your monthly payment is $1,596.18. The interest rate is 3.625%. How much will be owed after 30 years?
e) You take out a 15-year mortgage on a new home for $350,000. Your monthly payment is $2,523.63. The interest rate is 3.625%. How much will be owed after 30 years?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 2 Solution

Workshop Six Practice Exercises
Example 2: The FV Function
a) What is the future value of $11,000 that is going to be received today, if the appropriate interest rate is 11 percent?
b) What is the future value of $10,000 received every year for 8 years, if the going rate of interest is 12%?
c) You currently have $50,000 in retirement savings. You plan to add $7,000 every year until you retire in 20 years. If you earn 9% annually, how much will you have at retirement?
d) You take out a 30-year mortgage on a new home for $350,000. Your monthly payment is $1,596.18. The interest rate is 3.625%. How much will be owed after 30 years?
e) You take out a 15-year mortgage on a new home for $350,000. Your monthly payment is $2,523.63. The interest rate is 3.625%. How much will be owed after 30 years?
Check below for a detailed solution to this problem.
This example is designed to give you practice using Excel's FV function.
Input / Output area: The function operates in a very similar manner to the PV function.
This function can be used to calculate the Future Value of a single lump sum, of a level series of payments (an annuity), or a combination of the two.
Present Value Interest Rate Number of Periods Payments Future Value Examine the formulas in column G closely…
a) $ 11,000.00 11% 11 $ - $ 34,669.33 Note there is only one lump sum future value in this case, so the annuity payment "pmt" is set to zero.
b) $ - 12% 8 $ 10,000.00 $ 122,996.93 In this case there is a regular, repeating payment (an annuity), but no current present value payment.
c) $ 50,000.00 9% 20 $ 7,000.00 $ 638,341.38 This is a combination, with both annuity payments and an intial present value.
d) $ 350,000.00 3.625% 30 $ 1,596.18 $ 0.29 Note the adjustments in the formula here for monthly periods. The rate is divided by 12 (months per year) and the number of periods is multiplied by 12. Another approach would be to use a monthly rate and the number of months as the periods.
e) $ 350,000.00 3.625% 15 $ 2,523.63 $ 0.32 Note again the adjustments for monthly payments. Also, note the future value of a loan is always zero (with some round-off, we are going to pay it off!).
Watch the direction of the signs in the formulas here! The payments and the amounts owed can't be in the same direction!
In Finance, cash flows have direction. The convention is:
Cash flows away from us (like spending or depositing money) are indicated with a negative sign.
Cash flows towards us (like withdrawing or earning money) are indicated with a positive sign.
The basic syntax is: =FV(rate,nper,pmt,[pv],[type])
Where:
rate = The discount / interest rate to be applied
nper = The number of periods
pmt = The regular, level, repeating payment in each period (the annuity payments)
pv = The present value (a single lump sum at the beginning of periods) - This is optional. If not used, the function assumes it is zero.
type = Determines if the calculations are based on payments occurring at the beginning (an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
Optional and generally not used. If not used, the function assumes a zero value and an ordinary annuity (which is generally the case).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 3

Workshop Six Practice Exercises
Example 3: The NPER Function
a) How long will it take for $11,000 today to grow to $22,000 (double) if the interest rate is 10 percent?
b) We are trying to save $25,000 for a down payment on a new home. If we save $3,000 each year at a rate of 8%, how long will it take us to reach our goal?
c) You currently have $50,000 in retirement savings. You would like $1,000,000 before you retire. If you save $5,000 every year and earn 9% annually, how long do you have to wait?
d) You take out a standard mortgage on a new home for $350,000. Your monthly payment is $1,596.18. The interest rate is 3.625% annually. How long (how many months) will it take to pay off this home?
e) You take out a standard mortgage on a new home for $350,000. Your monthly payment is $1,596.18 but you have decided to pay an additional $500 each month to try to pay off the loan more quickly. The interest rate is 3.625% annually. How long (how many months) will it take to pay off this home?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 3 Solution

Workshop Six Practice Exercises
Example 3: The NPER Function
a) How long will it take for $11,000 today to grow to $22,000 (double) if the interest rate is 10 percent?
b) We are trying to save $25,000 for a down payment on a new home. If we save $3,000 each year at a rate of 8%, how long will it take us to reach our goal?
c) You currently have $50,000 in retirement savings. You would like $1,000,000 before you retire. If you save $5,000 every year and earn 9% annually, how long do you have to wait?
d) You take out a standard mortgage on a new home for $350,000. Your monthly payment is $1,596.18. The interest rate is 3.625% annually. How long (how many months) will it take to pay off this home?
e) You take out a standard mortgage on a new home for $350,000. Your monthly payment is $1,596.18 but you have decided to pay an additional $500 each month to try to pay off the loan more quickly. The interest rate is 3.625% annually. How long (how many months) will it take to pay off this home?
Check below for a detailed solution to this problem.
This example is designed to give you practice using Excel's NPER function.
Input / Output area: This function can be used to calculate the distance of time between a Future Value and a Present Value.
Present Value Interest Rate Number of Periods Payments Future Value Examine the formulas in column E closely…
a) $ 11,000.00 10% 7.27 $ - $ 22,000.00 Note there is only one lump sum present and future value in this case, so the annuity payment "pmt" is set to zero. This is also a demonstration of the famous "rule of 72". An investment will double in approximately 72 divided by the interest rate years!
b) $ - 8% 6.64 $ 3,000.00 $ 25,000.00 In this case there is a regular, repeating payment (an annuity), but no current present value payment.
c) $ 50,000.00 9% 26.72 $ 5,000.00 $ 1,000,000.00 This is a combination, with both annuity payments and an intial present value.
d) $ 350,000.00 3.625% 360.00 $ 1,596.18 $ - Note the adjustments in the formula here for monthly periods. The rate is divided by 12 (months per year) and the number of periods is multiplied by 12. Another approach would be to use a monthly rate and the number of months as the periods.
e) $ 350,000.00 3.625% 232.73 $ 2,096.18 $ - Note again the adjustments for monthly payments. Also, note the future value of a loan is always zero (with some round-off, we are going to pay it off!).
Watch the direction of the signs in the formulas here! The payments and the amounts owed can't be in the same direction!
In Finance, cash flows have direction. The convention is:
Cash flows away from us (like spending or depositing money) are indicated with a negative sign.
Cash flows towards us (like withdrawing or earning money) are indicated with a positive sign.
The basic syntax is: =NPER(rate,pmt,pv,[fv],[type])
Where:
rate = The discount / interest rate to be applied
pmt = The regular, level, repeating payment in each period (the annuity payments)
pv = The present value (a single lump sum at the beginning of periods)
fv = The future value (a single lump sum at the end of periods) - This is optional. If not used, the function assumes it is zero.
type = Determines if the calculations are based on payments occurring at the beginning (an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
Optional and generally not used. If not used, the function assumes a zero value and an ordinary annuity (which is generally the case).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 4

Workshop Six Practice Exercises
Example 4: The RATE Function
a) At what interest rate will $11,000 double to $22,000 in a period of 7.2 years?
b) We are trying to save $25,000 for a down payment on a new home. If we save $3,000 each year, what rate of return will we have to earn to reach our goal in 7 years?
c) You currently have $50,000 in retirement savings. You would like $1,000,000 before you retire in 20 years. If you save $5,000 every year what rate of return would you have to earn to reach your goal?
d) You take out a standard 30-year mortgage on a new home for $350,000. Your monthly payment is $1,596.18. What is the interest rate on your loan?
e) You take out a 15-year mortgage on a new home for $350,000. Your monthly payment is $2,523.63. What is the interest rate on your loan?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 4 Solution

Workshop Six Practice Exercises
Example 4: The RATE Function
a) At what interest rate will $11,000 double to $22,000 in a period of 7.2 years?
b) We are trying to save $25,000 for a down payment on a new home. If we save $3,000 each year, what rate of return will we have to earn to reach our goal in 7 years?
c) You currently have $50,000 in retirement savings. You would like $1,000,000 before you retire in 20 years. If you save $5,000 every year what rate of return would you have to earn to reach your goal?
d) You take out a standard 30-year mortgage on a new home for $350,000. Your monthly payment is $1,596.18. What is the interest rate on your loan?
e) You take out a 15-year mortgage on a new home for $350,000. Your monthly payment is $2,523.63. What is the interest rate on your loan?
Check below for a detailed solution to this problem.
This example is designed to give you practice using Excel's RATE function.
Input / Output area: This function can be used to calculate the rate required to get from a given Present Value to some Future Value over a pre-determined period of time.
Present Value Interest Rate Number of Periods Payments Future Value Examine the formulas in column D closely…
a) $ 11,000.00 10.1% 7.2 $ - $ 22,000.00 Note there is only one lump sum present and future value in this case, so the annuity payment "pmt" is set to zero. This is also a demonstration of the famous "rule of 72". An investment will double in approximately 72 divided by the interest rate years!
b) $ - 5.8% 7 $ 3,000.00 $ 25,000.00 In this case there is a regular, repeating payment (an annuity), but no current present value payment.
c) $ 50,000.00 13.1% 20 $ 5,000.00 $ 1,000,000.00 This is a combination, with both annuity payments and an intial present value.
d) $ 350,000.00 3.625% 30 $ 1,596.18 $ - Note the adjustments in the formula here for monthly periods. The number of periods is multiplied by 12. The rate that is then calculated is a monthly rate, so has to be multiplied by 12 to get the annual rate (APR).
e) $ 350,000.00 3.625% 15 $ 2,523.63 $ - Note again the adjustments for monthly payments. Also, note the future value of a loan is always zero (with some round-off, we are going to pay it off!).
Watch the direction of the signs in the formulas here! The payments and the amounts owed can't be in the same direction!
In Finance, cash flows have direction. The convention is:
Cash flows away from us (like spending or depositing money) are indicated with a negative sign.
Cash flows towards us (like withdrawing or earning money) are indicated with a positive sign.
The basic syntax is: =RATE(rate,pmt,pv,[fv],[type],[guess])
Where:
nper = The number of periods
pmt = The regular, level, repeating payment in each period (the annuity payments)
pv = The present value (a single lump sum at the beginning of periods)
fv = The future value (a single lump sum at the end of periods) - This is optional. If not used, the function assumes it is zero.
type = Determines if the calculations are based on payments occurring at the beginning (an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
Optional and generally not used. If not used, the function assumes a zero value and an ordinary annuity (which is generally the case).
guess = Guess at what the rate will be. Helps Excel with the iterative calculations, but is optional and usually not required.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 5

Workshop Six Practice Exercises
Example 5: The PMT Function
a) Your new-years resolution is to start saving for retirement. If you plan to retire in 20 years, and expect to earn 9% annually, how much will you have to save each year in order to reach your goal of $1,000,000 in retirement savings?
b) You currently have $50,000 in retirement savings. You plan to retire 20 years. If you earn 12% annually, how much will you have to save each year to reach your goal?
c) You take out a 30-year mortgage on a new home for $350,000. The interest rate is 3.625%. What will your monthly payment be?
d) To reward yourself for all your hard work in your degree program, you purchase a brand-new Mercedes G63 at a cost of $147,500? If you finance the car across 7 years at an interest rate of 7%, what will your monthly payment be?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 5 Solution

Workshop Six Practice Exercises
Example 5: The PMT Function
a) Your new-years resolution is to start saving for retirement. If you plan to retire in 20 years, and expect to earn 9% annually, how much will you have to save each year in order to reach your goal of $1,000,000 in retirement savings?
b) You currently have $50,000 in retirement savings. You plan to retire 20 years. If you earn 12% annually, how much will you have to save each year to reach your goal?
c) You take out a 30-year mortgage on a new home for $350,000. The interest rate is 3.625%. What will your monthly payment be?
d) To reward yourself for all your hard work in your degree program, you purchase a brand-new Mercedes G63 at a cost of $147,500? If you finance the car across 7 years at an interest rate of 7%, what will your monthly payment be?
Check below for a detailed solution to this problem.
This example is designed to give you practice using Excel's PMT function.
Input / Output area: This function can be used to calculate the annuity Payment required to get from a given Present Value to a Future Value at some Interest Rate.
It is very useful in a number of practical situations involving loans or savings plans!
Present Value Interest Rate Number of Periods Payments Future Value Examine the formulas in column F closely…
a) $ - 9% 20 $ 19,546.48 $ 1,000,000.00 In this case there is a regular, repeating payment (an annuity), but no current present value payment.
b) $ 50,000.00 12% 20 $ 7,184.84 $ 1,000,000.00 This is a combination, with both annuity payments and an intial present value.
c) $ 350,000.00 3.625% 30 $ 1,596.18 $ - Note the adjustments in the formula here for monthly periods. The rate is divided by 12 (months per year) and the number of periods is multiplied by 12. Another approach would be to use a monthly rate and the number of months as the periods.
d) $ 147,500.00 7.000% 7 $ 2,226.17 $ - Note again the adjustments for monthly payments. Also, note the future value of a loan is always zero (with some round-off, we are going to pay it off!).
Watch the direction of the signs in the formulas here! The payments and the amounts owed can't be in the same direction!
In Finance, cash flows have direction. The convention is:
Cash flows away from us (like spending or depositing money) are indicated with a negative sign.
Cash flows towards us (like withdrawing or earning money) are indicated with a positive sign.
The basic syntax is: =PMT(rate,nper,pv,[fv],[type])
Where:
rate = The discount / interest rate to be applied
nper = The number of periods
pv = The present value (a single lump sum at the beginning of periods)
fv = The future value (a single lump sum at the end of periods) - This is optional. If not used, the function assumes it is zero.
type = Determines if the calculations are based on payments occurring at the beginning (an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
Optional and generally not used. If not used, the function assumes a zero value and an ordinary annuity (which is generally the case).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 6

Workshop Six Practice Exercises
Example 6: Rate of Return Calculations with the IRR Function
The projected returns for an investment are shown in the table below:
.
Year: 0 1 2 3 4 5 6
Cash Flow $ (253,629.14) $ 50,000.00 $ 100,000.00 $ 145,000.00 $ 75,000.00 $ 25,000.00 $ 500.00
What is the expected Internal Rate of Return (IRR) for this investment?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 6 Solution

Workshop Six Practice Exercises
Example 6: Rate of Return Calculations with the IRR Function
The projected returns for an investment are shown in the table below:
.
Year: 0 1 2 3 4 5 6
Cash Flow $ (253,629.14) $ 50,000.00 $ 100,000.00 $ 145,000.00 $ 75,000.00 $ 25,000.00 $ 500.00
What is the expected Internal Rate of Return (IRR) for this investment?
Check below for a detailed solution to this problem.
This example is designed to give you practice using Excel's IRR function.
Input / Output area: This is a very useful and extremely easy-to-use function, but it has some quirks to watch out for!
.
Year: 0 1 2 4 5 Rate of Return
Cash Flow $ (253,629.14) $ 50,000.00 $ 100,000.00 $ 145,000.00 $ 75,000.00 $ 25,000.00 $ 500.00 17.8%
The first quirk to watch out for is the directionality of the cash flows.
If you enter a positive number for the Year Zero cash flow (a cost), the formula will return an error.
Also, note the IRR function assumes the cash flows you give it are in order, starting with today (Year 0).
Annoyingly, this is different from some other Excel functions, like the NPV function.
Finally, be aware the IRR function can "break" (stop functioning correctly) under certain unusual circumstances.
A classic example is when the cash flows change direction mid-stream. The IRR calculations will fail and give you an unreliable answer or an error.
When in doubt, just remember the NPV method is always reliable and the most robust approach!
The basic syntax is: =IRR(values,[guess])
Where:
values = The list of cash flows to be evaluated. Must be entered as a string, in order, starting with year zero. There can't be any blank cells in the string (zeros are OK).
guess = Guess at what the rate will be. Helps Excel with the iterative calculations, but is optional and usually not required.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 7

Workshop Six Practice Exercises
Example 7: Perpetuities
a) A share of Preferred Stock pays $5.20 in dividends annually. The promised rate of return is 6%. How much will these shares sell for?
b) If an investment costs $13,000 up front, but promises to return income of $1,700 every year thereafter, what is the implied rate of return of this investment?
c) You require the investments in your retirement portfolio to return a minimum of 8%. Your insurance agent offers to sell you an investment for $105,000 that pays income every year indefinitely. What must the annual cash flow from this investment be to meet your criteria?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 7 Solution

Workshop Six Practice Exercises
Example 7: Perpetuities
a) A share of Preferred Stock pays $5.20 in dividends annually. The promised rate of return is 6%. How much will these shares sell for?
b) If an investment costs $13,000 up front, but promises to return income of $1,700 every year thereafter, what is the implied rate of return of this investment?
c) You require the investments in your retirement portfolio to return a minimum of 8%. Your insurance agent offers to sell you an investment for $105,000 that pays income every year indefinitely. What must the annual cash flow from this investment be to meet your criteria?
Check below for a detailed solution to this problem.
These problems each use a variation of the standard formula for a perpetuity, which is found on page 255 of the Higgins textbook.
Input / Output area: The math is very simple, but you will need to be able to manipulate the formula to find what you need.
Present Value Cash Flow Rate of Return
a) $ 86.67 $ 5.20 6.0% PV = A / r This is the standard version of the equation shown on page 255 of the Higgins textbook.
Note the rate has to either be entered as a percentage or as a decimal. Whole numbers will not work! ("6" is actually 600%!)
b) $ 13,000.00 $ 1,700.00 13.1% r = A / PV This is a variation of the perpetuity formula, also found on page 255 of the Higgins textbook.
c) $ 105,000.00 $ 8,400.00 8.0% A = PV * r This is a variation of the perpetuity formula found on page 255 of the Higgins textbook.
It takes a little bit of algebra to get there.
Just remember if you are given any two of the variables in the perpetuity equation, you can solve for the third variable!
Note an annuity pays a level series of payments for a fixed period of time.
A perpetuity, however, pays a level series of payments forever.
The math behind a perpetuity is much simpler than an annuity.
As a result, many investments are treated as perpetuities (like shares of stock).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 8

Workshop Six Practice Exercises
Example 8: Payback Period
The expected free cash flow from an investment is shown in the table below:
.
Year Cash Flow
0 $ (500,000.00)
1 $ 50,000.00
2 $ 125,000.00
3 $ 175,000.00
4 $ 125,000.00
5 $ 100,000.00
What is the Payback Period for this investment?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 8 Solution

Workshop Six Practice Exercises
Example 8: Payback Period
The expected free cash flow from an investment is shown in the table below:
.
Year Cash Flow
0 $ (500,000.00)
1 $ 50,000.00
2 $ 125,000.00
3 $ 175,000.00
4 $ 125,000.00
5 $ 100,000.00
What is the Payback Period for this investment?
Check below for a detailed solution to this problem.
Input area: The Payback Period is a commonly-used method for small capital budgeting decisions, primarily due to its simplicity.
Just be warned it has a number of deficiencies (see your textbook) and should not be used for large, strategic decisions.
Cash Flows: One easy way to visualize the payback point is to calculate the cumulative cash flows through the project's lifetime…
Initial Cost $ (500,000.00) $ (500,000.00) Just getting started. We are now in a hole…
Year 1 Cash Inflow $ 50,000.00 $ (450,000.00) With each cash inflow benefit, we dig deeper out of the hole…
Year 2 Cash Inflow $ 125,000.00 $ (325,000.00)
Year 3 Cash Inflow $ 175,000.00 $ (150,000.00)
Year 4 Cash Inflow $ 125,000.00 $ (25,000.00) You can see the initial cost / investment is being paid off… Not quite yet though!
Year 5 Cash Inflow $ 100,000.00 $ 75,000.00 <--- You can see here this particular investment finally "pays back" somewhere between the 4th and 5th year.
We could say the project pays back in less than 5 years, or better yet, calculate the precise payback period as shown below!
Output area:
Payback Period (years) 4.25 <--- Examine the formula here: There is $25,000 left to be paid off on the project after 4 years.
However, $100,000 is going to be received during the fourth year.
So it will take $25,000 / $100,000 = 0.25 years after year 4 to pay off the initial investment!
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 9

Workshop Six Practice Exercises
Example 9: The NPV Function
The expected free cash flow from an investment is shown in the table below:
. .
Year Cash Flow
1 $ 150,000.00
2 $ 50,000.00
3 $ 175,000.00
4 $ 25,000.00
5 $ 125,000.00
6 $ 50,000.00
The cost of this investment is $400,000. The company's Board of Directors requires an 11% return on all investments.
a) What is the Net Present Value of this investment?
b) What is the Benefit-Cost Ratio (Profitability Index) of this investment?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 9 Solution

Workshop Six Practice Exercises
Example 9: The NPV Function
The expected free cash flow from an investment is shown in the table below:
. .
Year Cash Flow
1 $ 150,000.00
2 $ 50,000.00
3 $ 175,000.00
4 $ 25,000.00
5 $ 125,000.00
6 $ 50,000.00
The cost of this investment is $400,000. The company's Board of Directors requires an 11% return on all investments.
a) What is the Net Present Value of this investment?
b) What is the Benefit-Cost Ratio (Profitability Index) of this investment?
Check below for a detailed solution to this problem.
Input area: This example is designed to give you practice using Excel's NPV function.
This is another very easy function to use, but again it has some quirks you should be aware of.
Remember the Net Present Value = The Present Value of the cash inflows (benefits) minus the initial cash outflow (costs)
Cash Flows: The Benefit-Cost Ratio (also called the Profitability Index) = The Present Value of the cash inflows DIVIDED by the initial cash outflow (costs)
Initial Cost $ (400,000.00)
Year 1 Cash Inflow $ 150,000.00
Year 2 Cash Inflow $ 50,000.00
Year 3 Cash Inflow $ 175,000.00
Year 4 Cash Inflow $ 25,000.00
Year 5 Cash Inflow $ 125,000.00
Year 6 Cash Inflow $ 50,000.00
Required Return (%) 11.0%
Output area:
a) Net Present Value $ 21,056.48 Note the cost is subtracted to get Net Present Value. This is a positive number, so the project should be accepted.
b) Benefit-Cost Ratio 0.053 Not very impressive, but still acceptable.
Watch the signs with these numbers. If the Present Value of the cash inflows exceeds the cost, you should wind up with a positive number!
The basic syntax is: =NPV(rate,value1,[value2],[value3]…)
Where:
rate = The discount / interest rate to be applied
values = The list of cash flows to be evaluated. Can be entered as a string or separately, in order, starting with year One. There can't be any blank cells in the string (zeros are OK).
The first quirk to watch out for is the directionality of the cash flows.
For example, be careful not to subtract a negative number for the cost (the negative signs cancel, meaning you will actually add the cost)
Second, note the NPV function doesn't actually calculate Net Present Value!
It only calculated the Present Value of a list of cash flows. To get the NET Present Value, you have to subtract the initial cost yourself.
Third, note the NPV function assumes the cash flows you give it are in order, starting with one period from today (like Year 1).
Annoyingly, this is different from some other Excel functions, like the IRR function.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 10

Workshop Six Practice Exercises
Example 10: Capital Budgeting
The expected free cash flow from an investment is shown in the table below:
.
Year Cash Flow
1 $ 5,500.00
2 $ 12,500.00
3 $ 15,000.00
4 $ 17,500.00
5 $ 20,000.00
6 $ 17,500.00
The cost of this investment is $65,000 million. The company's owner requires a 9% return on all investments.
a) What is the Payback Period of this investment?
b) What is the Net Present Value of this investment?
c) What is the Benefit-Cost Ratio (Profitability Index) of this investment?
d) What is the Internal Rate of Return (IRR) of this investment?
e) Based on the company's criterion, should it pursue this investment?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 10 Solution

Workshop Six Practice Exercises
Example 10: Capital Budgeting
The expected free cash flow from an investment is shown in the table below:
.
Year Cash Flow
1 $ 5,500.00
2 $ 12,500.00
3 $ 15,000.00
4 $ 17,500.00
5 $ 20,000.00
6 $ 17,500.00
The cost of this investment is $65,000 million. The company's owner requires a 9% return on all investments.
a) What is the Payback Period of this investment?
b) What is the Net Present Value of this investment?
c) What is the Benefit-Cost Ratio (Profitability Index) of this investment?
d) What is the Internal Rate of Return (IRR) of this investment?
e) Based on the company's criterion, should it pursue this investment?
Check below for a detailed solution to this problem.
Input area: This is an example of a full capital budgeting problem, with multiple evaluation techiques utilized.
The key is not only to be able to do the math, but also to interpret the results!
Cash Flows: Cumulative Cash Flows
Initial Cost $ (65,000.00) $ (65,000.00)
Year 1 Cash Inflow $ 5,500.00 $ (59,500.00)
Year 2 Cash Inflow $ 12,500.00 $ (47,000.00)
Year 3 Cash Inflow $ 15,000.00 $ (32,000.00)
Year 4 Cash Inflow $ 17,500.00 $ (14,500.00) <--- Note the project pays back somewhere between years 4 and 5
Year 5 Cash Inflow $ 20,000.00 $ 5,500.00
Year 6 Cash Inflow $ 17,500.00 $ 23,000.00
Required Return (%) 9.0%
Output area:
a) Payback Period (years) 4.7 Although the project does pay back before it ends, that doesn't mean we should accept it!
Remember the payback method has problems, including the fact it ignores the time value of money.
b) Net Present Value $ (2,019.63) This is a negative number, meaning the present value of the project's benefits do not outweight its cost
We should reject this project!
c) Benefit-Cost Ratio -0.031 The BCR also indicates the project should be rejected.
d) Internal Rate of Return 8.10% The IRR is less than the required return, so again this indicates the project should be rejected.
e) Should be pursued? No! The NPV is negative, so the project should be rejected.
When multiple approaches disagree, always go with what NPV tells you to do!
Examine each of these formulas above closely.
A few common problems here include:
(1) Getting confused about the payback calculations, perhaps swapping negatives or cash flows across years.
(2) Using the NPV function on the entire stream of cash flows, including the Year Zero cost.
This will result in a very plausible, but incorrect number.
Excel's NPV function assumes the first cash flow in the string is one year in the future (unlike IRR).
(3) Getting the signs wrong on the BCR.
(4) Not including the Year Zero cost in the IRR function.
Microsoft famously programmed the IRR function to assume the first cash flow in the stream is Year Zero (unlike NPV). Very annoying!
(5) Not making the simple accept /reject decision based on NPV or IRR.
This is the student Practice Problem file, provided in the assignment instructions October 2019