Financial Valuation and Quantitative Techniques Excel Template Problem Sets (2 Assignments)

Anonymous
timer Asked: Nov 3rd, 2017
account_balance_wallet $30

Question Description

Hello I am currently in a graduate master's program business math course and I have two excel template problem sets that are posted that deals with problems with regards to annuities and financial investments for the most part. Math has never been a strong point and I would really appreciate any help I can receive on this for better understanding.

Side Note: If possible, I would kindly request also a separate "word document" to see how each problem got solved for better understanding of how to set up financial math problems when it comes to deriving the solution for each one that I am struggling with for both these two different excel problem set assignments.

Purpose of Assignment (First Assignment "Financial Valuation (Time-Value of Money Cases)" Excel template attached below.

The purpose of this assignment is to provide students an opportunity to apply the concepts of time value of money covered in Ch. 13 to integrated case studies.

Assignment Steps

Resources: Financial Valuation (Time-Value of Money) Cases Excel® Template

Save the Financial Valuation (Time-Value of Money) Cases Excel® Template to your computer.

Read the instructions on the first tab.

Complete the three cases located in the template.

Click the Assignment Files tab to submit your assignment.

Purpose of Assignment (Second Assignment "Quantitative Techniques in Financial Valuation Problem Set") Excel template attached below

The purpose of this assignment is to provide students an opportunity to practice and learn the time-value of money concepts covered during Week 4. Students will understand how to evaluate future values, present values, interest rates, and time periods for financial investments.

Assignment Steps

Resources: Quantitative Techniques in Financial Valuation Problem Set Excel® Template

Save the Quantitative Techniques in Financial Valuation Problem Set Excel® Template to your computer.

Read the instructions on the first tab.

Complete the twelve exercises located in the template and record your answers in the highlighted spaces.

Format your paper consistent with APA guidelines.

Click the Assignment Files tab to submit your assignment.

Unformatted Attachment Preview

Barry learned in an online investment course that he should start investing as soon as possible. He had always thought that it would be smart to start investing after he finishes college and when his salary is high enough to pay the bills and to have money left over. He projects that will be 5–10 years from now. Barry wants to compare the difference between investing now and investing later. A financial advisor who spoke to Barry suggested that a Roth IRA (Individual Retirement Account) would be a good investment for him to start. 1. If Barry purchases a $2,000 Roth IRA when he is 25 years old and expects to earn an average of 6% per year compounded annually over 35 years (until he is 60), how much will accumulate in the investment? Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Choose one For Quarterly, type 4; for semiannually, type 2; for annually, ty 2. If Barry doesn’t put the money in the IRA until he is 35 years old, how much money will accumulate in the account by the time he is 60 years old using the same return of 6%? How much less will he earn because he invested 10 years later? Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Choose one For Quarterly, type 4; for semiannually, type 2; for annually, ty Difference in amount earned FV Part 1 minus FV Part 2 3. Barry knows that the interest rate is critical to the speed at which your investment grows. For instance, if $1 is invested at 2% compounded annually, it takes approximately 34.9 years to double. If $1 is invested at 5% compounded annually, it takes approximately 14.2 years to double. Determine how many years it takes $1 to double if invested at 10% compounded annually; at 12% compounded annually. Hint: The easiest way to get the answer is to use the Rule of 72. Years to double the investment = 72 ÷ interest rate 4. At what interest rate would you need to invest to have your money double in 10 years if it is compounded annually? PV FV NPER RATE -- Use the RATE function in Excel. PV should be negative, FV sho ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 Abdol Akhim has just come from a Personal Finance class where he learned that he can determine how much his savings will be worth in the future. Abdol is completing his two-year business administration degree this semester and has been repairing computers in his spare time to pay for his tuition and books. Abdol got out his savings records and decided to apply what he had learned. He has a balance of $1,000 in a money market account at First Savings Bank, and he considers this to be an emergency fund. His instructor says that he should have 3–6 months of his total bills in an emergency fund. His bills are currently $700 a month. He also has a checking account and a regular savings account at First Savings Bank, and he will shift some of his funds from those accounts into the emergency fund. One of Abdol’s future goals is to buy a house. He wants to start another account to save the $8,000 he needs for a down payment. 1. How much interest will Abdol receive on $1,000 in a 365-day year if he keeps it in the money market account earning 1.00% compounded daily? Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Compound Interest Choose one For Quarterly, type 4; for semiannually, type 2; for annually 2. How much money must Abdol shift from his other accounts to his emergency fund to have four times his monthly bills in the account by the end of the year? Desired Emergency fund Current balance in money mkt. Interest that Abdol will earn Balance to be transferred 3. Abdol realizes he needs to earn more interest than his current money market can provide. Using annual compounding on an account that pays 5.5% interest annually, find the amount Abdol needs to invest to have the $8,000 down payment for his house in 5 years. Future Value Needed (FV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Amount Invested Now (PV) Choose one For Quarterly, type 4; for semiannually, type 2; for annually 4. Is 5.5% a realistic rate for Abdol to earn in a relatively short-term investment of 5 years, particularly at his bank? Hint: For answering this question, explore how much interest do banks pay on short-term investments or CDs. Compare this number with 5.5% to see whether it is a realistic goal. If not, propose to Abdol what should he invest in instead. ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 At 45 years of age, Seth figured he wanted to work only 10 more years. Being a full-time landlord had a lot of advantages: cash flow, free time, being his own boss—but it was time to start thinking toward retirement. The real estate investments that he had made over the last 15 years had paid off handsomely. After selling a duplex and paying the associated taxes, Seth had $350,000 in the bank and was debt-free. With only 10 years before retirement, Seth wanted to make solid financial decisions that would limit his risk exposure. Fortunately, he had located another property that seemed to meet his needs— a well maintained four-unit apartment. The price tag was $250,000, well within his range, and the apartment would require no remodeling. Seth figured he could invest the other $100,000, and between the two hoped to have $1 million to retire on by age 55. 1. Seth read an article in the local newspaper stating the real estate in the area had appreciated by 5% per year over the last 30 years. Assuming the article is correct, what would the future value of the $250,000 apartment be in 10 years? Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Choose one For Quarterly, type 4; for semiannually, type 2; for annu 2. Seth’s current bank offers a 1-year certificate of deposit account paying 2% compounded semiannually. A competitor bank is also offering 2%, but compounded daily. If Seth invests the $100,000, how much more money will he have in the second bank after one year, due to the daily compounding? Current Bank Semiannually Competitor Bank Daily Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Choose one For Quarterly, type 4; for semiannu Difference in FV =D36-C36 3. After looking at the results from questions 1 and 2, Seth realizes that a 2% return in a certificate of deposit will never allow him to reach his goal of $1 million in 10 years. Presuming his apartment will indeed be worth $400,000 in 10 years, compute the future value of Seth’s $100,000 investment using a 10%, 15%, and 20% return compounded semiannually for 10 years. Will any of these rates of return allow him to accomplish his goal of reaching $1 million by age 55? 10% Initial Investment (PV) 15% 20% Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Plus: Apartment Value Total FV Semiannually Semiannually Semiannually $400,000 $400,000 $400,000 Which rate of return allows him to accomplish his goal of reaching $1 million? 4. A friend of Seth’s who is a real estate developer needs to borrow $80,000 to finish a development project. He is desperate for cash and offers Seth 18%, compounded monthly, for 2.5 years. Find the future value of the loan. Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Choose one For Quarterly, type 4; for semiannually, type 2; for annu 5. After purchasing the apartment, Seth receives a street, sewer, and gutter assessment for $12,500 due in 2 years. How much would he have to invest today in a CD paying 2%, compounded semiannually, to fully pay the assessment in 2 year Future Value Needed (FV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Amount Invested Now (PV) Choose one For Quarterly, type 4; for semiannually, type 2; for annu retirement. ter selling a only 10 years re. Fortunately, partment. The eth figured he by 5% per year 00 apartment emiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 arterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 e of deposit ed be worth and 20% return h his goal of =FV + Apartment Value Choose one ent project. emiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 500 due in 2 years. pay the assessment in 2 years? emiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 Instructions: Week 5 Individual Assignment Total Number of Questions - 12 Total Points: 6 1. You have twelve problems - on each tab of this Excel file. 2. Please show your work in the cells. Use Excel formulas instead of writing the values/answers directly in the cell. The instructor will then know where you made a mistake and provide you valuable feedback and partial credit (if appropria back and partial credit (if appropriate). Find the interest paid on a loan of $1,200 for three years at a simple interest rate of 5% per year. How much money will you pay after three years? Principal Rate Time Simple Interest (SI) Maturity Value Find the maturity value of a loan of $1,750 for 28 months at 9.8% simple interest per year. Principal Rate Time Simple Interest (SI) Maturity Value -- Please make sure that the time periods for Time and Rate match. Find the simple interest rate of a loan of $5,000 that is made for three years and requires $1,762.50 in interest. Principal Time SI Rate 762.50 in interest. A loan of $16,840 is borrowed at 9% simple interest and is repaid with $4,167.90 interest. What is the duration of the loan? Principal Rate SI Time How much money is borrowed if the interest rate is 9.25% simple interest and the loan is made for 3.5 years and has $904.88 interest? SI Rate Time Principal Find the ordinary and exact interest for a loan of $1000 at a 5% annual interest rate. The loan was made on March 15 and is due May 15. Loan date Loan Due Date Exact time Principal Rate Time Ordinary Simple Interest (SI) days Loan date Loan Due Date Exact time Principal Rate Time Exact Simple Interest (SI) days Find the bank discount and proceeds using ordinary interest for a loan to Michelle Anders for $7,200 at 8.25% annual simple interest from August 8 to November 8. Loan date Loan Due Date Exact time Face Value (F) Discount Rate (D) Time Period (T) Bank Discount (B) Proceeds (P) days years --> 'Convert Exact time in days to years (DATEDIF(0, A10, Y) What is the effective interest rate of a simple discount note for $8,000, at an ordinary bank discount rate of 11%, for 120 days? Face Value (F) Discount Rate (D) Time Period (T) Bank Discount (B) Proceeds (P) Rate years --> 'Convert Exact time in days to years DATEDIF(0, A6, Y) SOLVED EXAMPLE What is the effective interest rate for the first year for a loan of $20,000 for three years if the interest is compounded quarterly at a rate of 12%? Quoted Rate 12.00% quarterly No. of compounding periods per year EAR 4 For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo 12.55% 1. Ross Land has a loan of $8,500 compounded quarterly for four years at 6%. What is the effective interest rate for the first y Quoted Rate No. of compounding periods per year EAR For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo 2. Find the effective interest rate for the first year for a loan for four years compounded semiannually at an annual rate of 2% Quoted Rate No. of compounding periods per year EAR For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo 3. What is the effective interest rate for the first year for a loan of $5,000 at 10% compounded daily for three years? Quoted Rate No. of compounding periods per year EAR For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo 4. Depending on the issuer, a typical credit card agreement quotes an interest rate of 18 percent APR. Monthly payments are What is the actual interest rate you pay on such a credit card? Quoted Rate No. of compounding periods per year EAR For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo =EFFECT(B30, B31) 5. Find the effective interest rate for a loan of $3,500 at 10% interest compounded quarterly. Quoted Rate No. of compounding periods per year EAR For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; fo =EFFECT(B36, B37) SOLVED EXAMPLE Tim Bowling has $20,000 invested for three years at a 5.25% annual rate compounded daily. How much interest will he earn? Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Compound Interest $20,000 5.25% Daily 365 0.0144% 3 1095 $23,411.35 $3,411.35 Choose one For Quarterly, type 4; for semiannually, type 2; for a Exercise Find the future value of a $15,000 money market investment at 2.8% annual interest compounded daily for three years. Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Compound Interest for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 SOLVED EXAMPLE The Holiday Boutique would like to put away some of the holiday profits to save for a planned expansion. A total of $8,000 is needed in three years. How much money in a 5.2% three-year certificate of deposit that is compounded monthly must be invested now to have the $8,000 in three years? Future Value Needed (FV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Amount Invested Now (PV) $8,000 5.2% Monthly 12 0.4333% 3 36 $6,846.78 Choose one For Quarterly, type 4; for semiannually, type 2; for a Exercise How much should be invested now to have $15,000 in six years if interest is 4% compounded quarterly? Future Value Needed (FV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Amount Invested Now (PV) r semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 Jamie Juarez needs $12,000 in 10 years for her daughter’s college education. How much must be invested today at 2% annual interest compounded semiannually to have the needed funds? Future Value Needed (FV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Amount Invested Now (PV) Choose one For Quarterly, type 4; for semiannually, type 2; for annually, ty A loan of $8,000 for two acres of woodland is compounded quarterly at an annual rate of 6% for five years. Find the compound amount and the compound interest. Initial Investment (PV) Quoted Rate Compounding Frequency Number of compoundings (m) Quoted Rate divided by m = RATE Number of Years NPER (Num. of years * m) Ending Amount (FV) Compound Interest Choose one For Quarterly, type 4; for semiannually, type 2; for annually, ty ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365 ...
Purchase answer to see full attachment

Tutor Answer

Tutor_Booth
School: Cornell University

Attached.

Barry learned in an online investment course that he should start investing as soon
as possible. He had always thought that it would be smart to start investing after he
finishes college and when his salary is high enough to pay the bills and to have money
left over. He projects that will be 5–10 years from now. Barry wants to compare the
difference between investing now and investing later. A financial advisor who spoke
to Barry suggested that a Roth IRA (Individual Retirement Account) would be a good
investment for him to start.
1. If Barry purchases a $2,000 Roth IRA when he is 25 years old and expects to
earn an average of 6% per year compounded annually over 35 years (until he is
60), how much will accumulate in the investment?
Initial Investment (PV)
Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Ending Amount (FV)

$2.000
6,00%
Annually Choose one
1
For Quarterly, type 4; for semiannually, type 2; for annually, ty
6,0000%
35
35
$15.372,17

2. If Barry doesn’t put the money in the IRA until he is 35 years old, how much
money will accumulate in the account by the time he is 60 years old using the same
return of 6%? How much less will he earn because he invested 10 years later?
Initial Investment (PV)
Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Ending Amount (FV)

$2.000
6,00%
Annually
1
6,0000%
25
25
$8.583,74

Difference in amount earned
FV Part 1 minus FV Part 2

$6.788,43

Choose one
For Quarterly, type 4; for semiannually, type 2; for annually, ty

3. Barry knows that the interest rate is critical to the speed at which your investment grows.
For instance, if $1 is invested at 2% compounded annually, it takes approximately 34.9 years
to double. If $1 is invested at 5% compounded annually, it takes approximately
14.2 years to double.
Determine how many years it takes $1 to double if invested at 10% compounded annually; at
12% compounded annually.
Present value (PV)
Future value (FV)

1
2

1
2

Qouted rate
Number of years

10%
7,2725

12%
6,1163

4. At what interest rate would you need to invest to have your money double
in 10 years if it is compounded annually?
PV
FV
NPER
RATE

$2.000
$4.000
10
7,18% -- Use the RATE function in Excel. PV should be negative, FV sho

ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

Abdol Akhim has just come from a Personal Finance class where he learned that he
can determine how much his savings will be worth in the future. Abdol is completing
his two-year business administration degree this semester and has been repairing
computers in his spare time to pay for his tuition and books. Abdol got out his savings
records and decided to apply what he had learned. He has a balance of $1,000 in a
money market account at First Savings Bank, and he considers this to be an emergency
fund. His instructor says that he should have 3–6 months of his total bills in an
emergency fund. His bills are currently $700 a month. He also has a checking account and a
regular savings account at First Savings Bank, and he will shift some of his funds from
those accounts into the emergency fund. One of Abdol’s future goals is to buy a house.
He wants to start another account to save the $8,000 he needs for a down payment.
1. How much interest will Abdol receive on $1,000 in a 365-day year if he keeps
it in the money market account earning 1.00% compounded daily?
Initial Investment (PV)
Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Ending Amount (FV)
Compound Interest

$1.000
1,00%
Daily
365
0,0027%
1
365
$1.010,05
$10,05

Choose one
For Quarterly, type 4; for semiannually, type 2; for annually

2. How much money must Abdol shift from his other accounts to his emergency fund
to have four times his monthly bills in the account by the end of the year?
Desired Emergency fund
Current balance in money mkt.
Interest that Abdol will earn
Balance to be transferred

$2.800
$1.000
$10,05
$1.789,95

3. Abdol realizes he needs to earn more interest than his current money market can provide.
Using annual compounding on an account that pays 5.5% interest annually, find the amount
Abdol needs to invest to have the $8,000 down payment for his house in 5 years.
Future Value Needed (FV)
Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Amount Invested Now (PV)

$8.000
5,5%
Annually
1
5,5000%
5
5
$6.121,07

Choose one
For Quarterly, type 4; for semiannually, type 2; for annually

4. Is 5.5% a realistic rate for Abdol to earn in a relatively short-term investment of 5 years, particularly at his bank?

Hint: For answering this question, explore how much interest do banks pay on short-term investments or CDs.
Compare this number with 5.5% to see whether it is a realistic goal. If not, propose to Abdol what should he invest in
instead.
Abdols compounding account that pays 5.5% in a short term investment of
5 years is no realistic. After exploring the interest rate which banks pay on a
short-term investment or CDs, its concluded that the nations average rate is
about 2.10% annually. In comparing the 5.5% rate to the 2.10% rate there
is a 3.4% rate difference. In order for Abdol to obtain $8,000 he would have
to invest $7,210.43 and that is the 2.10% rate annually for 5 years.

Future Value Needed (FV)
Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Amount Invested Now (PV)

$8.000
2,1%
Annually
1
2,1000%
5
5
$7.210,43

ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

ually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

At 45 years of age, Seth figured he wanted to work only 10 more years. Being a full-time landlord had a lot
of advantages: cash flow, free time, being his own boss—but it was time to start thinking toward retirement.
The real estate investments that he had made over the last 15 years had paid off handsomely. After selling a
duplex and paying the associated taxes, Seth had $350,000 in the bank and was debt-free. With only 10 years
before retirement, Seth want...

flag Report DMCA
Review

Anonymous
Good stuff. Would use again.

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors