Part A You have to use the Excel spreadsheet Suppose that a savings account is opened with an initial deposit of $2,000 Also, the account gets the interest that is compounded each month at an interest rate that is increasing each month as follows 0 10 , 0 11 , 0 12 , 0 13 , 0 14 , 0 15 , For each of the following three deposit scenarios (A), (B), and (C) described below, do the following (i) Use the Excel to construct a single iterative equation for the account each month Pn (ii) Use the Excel to iterate the equation a sufficient number of times to determine how long it takes for the balance to reach $10,000 (iii) Use the Excel to draw a time series graph of Pn for this time interval Three deposit scenarios (A) $100 is deposit at the end of every month (B) The amounts deposited at the end of subsequent months are $100, $50, $100, $50, $100, $50, $100, $50, (C) The amounts deposited at the end of subsequent months are $100, $150, $100, $175, $187 50, $193 75, Part B Continuous Compounding Compute the FV of $1,900 continuously compounded for A 7 years at a stated annual rate of 12 B 5yearsatastatedannualrateof10 C 12yearsatastatedannualrateof5 D 10 years at a stated annual rate of 7 Interest Rates Well known financial writer Andrew Tobias argues that he can earn 177 percent per year buying wine by the case Specifically, he assumes that he will consume one $10 bottle of fine Bordeaux per week for the next 12 weeks He can either pay $10 per week or buy a case of 12 bottles today If he buys the case, he receives a 10 percent discount and, by doing so, earns the 177 percent Assume he buys the wine and consumes the first bottle today Do you agree with his analysis Do you see a problem with his numbers Calculating EAR Friendlys Quick Loans, Inc , offers you three for four or I knock on your door This means you get $3 today and repay $4 when you get your paycheck in one week (or else) What's the effective annual return Friendly's earns on this lending business If you were brave enough to ask, what APR would Friendly's say you were paying Growing Perpetuities Mark Weinstein has been working on an advanced technology in laser eye surgery His technology will be available in the near term He anticipates his first annual cash flow from the technology to be $175,000, received two years from today Subsequent annual cash flows will grow at 3 5 percent in perpetuity What is the present' value of the technology if the discount rate is 10 percent Balloon Payments Audrey Sanborn has just arranged to purchase a $550,000 vacation home in the Bahamas with a 20 percent down payment The mortgage has a 6 1 percent stated annual interest rate, compounded monthly, and calls for equal monthly payments over the next 30 years Her first payment will be due one month from now However, the mortgage has an eight year balloon payment, meaning that the balance of the loan must be paid off at the end of Year 8 There were no other transaction costs or finance charges How much will Audreys balloon payment be in eighty years Growing Annuity Your job pays you only once a year for all the work you did over the previous 12 months Today, December 31, you just received your salary of $65,000, and you plan to spend all of it However, you want to start saving for retirement beginning next year You have decided that one year from today you will begin depositing 5 of your annual salary in an account that will earn 10 per year Your salary will increase at 4 per year throughout your career How much money will you have on the date of your retirement 40 years from today Loan Payments You need a 30 year, fixed rate mortgage to buy a new home for $250,000 Your mortgage bank will lend you the money at a 5 3 APR for this 360 month loan However, you can only afford monthly payments of $950, so you offer to pay off any remaining loan balance at the end of the loan in the form of a single balloon payment How large will this balloon payment have to be for you to keep your monthly payments at $950 8 Present Value and Break Even Interest Consider a firm with a contract to sell an asset for $115,000 three years from now The asset costs $76,000 to produce today Given a relevant discount rate on this asset of 13 per year, will the firm make a profit on this asset At what rate does the firm just break even Variable Interest Rates A 15 year annuity pays $1,500 per month, and payments are made at the end of each month If the interest rate is 12 compounded monthly for the first seven years, and 6 compounded monthly thereafter, what is the present value of the annuity Annuities Youaresavingforthecollegeeducationofyourtwochildren Theyaretwoyearsapartinage onewillbegincollege15yearsfromtodayandtheotherwillbeginl7yearsfromtoday Youestimateyourchildrenscollegeexpensesto$45,000peryearperchild,payableatthebeginningofeachschoolyear Theannualinterestrateis7 5 Howmuchmoneymustyoudepositinanaccounteachyeartofundyourchildrenseducation Yourdepositsbeginoneyearfromtoday Youwillmakeyourlastdepositwhenyouroldestchildenterscollege Assumefouryearsofcollege In excel please

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 09, 2024

Part A: You have to use the Excel spreadsheet. Suppose that a savings account is opened with an initial deposit of $2,000. Also, the account

Part A: You have to use the Excel spreadsheet.

Suppose that a savings account is opened with an initial deposit of $2,000. Also, the account gets the interest that is compounded each month at an interest rate that is increasing each month as follows:

0.10%, 0.11%, 0.12%, 0.13%, 0.14%, 0.15%, ..........

For each of the following three deposit scenarios (A), (B), and (C) described below, do the following:

(i) Use the Excel to construct a single iterative equation for the account each month Pn.
(ii) Use the Excel to iterate the equation a sufficient number of times to determine how long it takes for the balance to reach $10,000.
(iii) Use the Excel to draw a time series graph of Pn for this time interval.

Three deposit scenarios:

(A) $100 is deposit at the end of every month.
(B) The amounts deposited at the end of subsequent months are: $100, $50, $100, $50, $100, $50, $100, $50, .........
(C) The amounts deposited at the end of subsequent months are: $100, $150, $100, $175, $187.50, $193.75, .........

Part B:

[Continuous Compounding]: Compute the FV of $1,900 continuously compounded for: A. 7 years at a stated annual rate of 12% B. 5yearsatastatedannualrateof10% C. 12yearsatastatedannualrateof5% D. 10 years at a stated annual rate of 7%
[Interest Rates]: Well-known financial writer Andrew Tobias argues that he can earn 177 percent per year buying wine by the case. Specifically, he assumes that he will consume one $10 bottle of fine Bordeaux per week for the next 12 weeks. He can either pay $10 per week or buy a case of 12 bottles today. If he buys the case, he receives a 10 percent discount and, by doing so, earns the 177 percent. Assume he buys the wine and consumes the first bottle today. Do you agree with his analysis? Do you see a problem with his numbers?
[Calculating EAR]: Friendlys Quick Loans, Inc., offers you three for four or I knock on your door. This means you get $3 today and repay $4 when you get your paycheck in one week (or else). What's the effective annual return Friendly's earns on this lending business? If you were brave enough to ask, what APR would Friendly's say you were paying?
[Growing Perpetuities] Mark Weinstein has been working on an advanced technology in laser eye surgery. His technology will be available in the near term. He anticipates his first annual cash flow from the technology to be $175,000, received two years from today. Subsequent annual cash flows will grow at 3.5 percent in perpetuity. What is the present' value of the technology if the discount rate is 10 percent?
[Balloon Payments]: Audrey Sanborn has just arranged to purchase a $550,000 vacation home in the Bahamas with a 20 percent down payment. The mortgage has a 6.1 percent stated annual interest rate, compounded monthly, and calls for equal monthly payments over the next 30 years. Her first payment will be due one month from now. However, the mortgage has an eight-year balloon payment, meaning that the balance of the loan must be paid off at the end of Year 8. There were no other transaction costs or finance charges. How much will Audreys balloon payment be in eighty years?

[Growing Annuity]: Your job pays you only once a year for all the work you did over the previous 12 months. Today, December 31, you just received your salary of $65,000, and you plan to spend all of it. However, you want to start saving for retirement beginning next year. You have decided that one year from today you will begin depositing 5% of your annual salary in an account that will earn 10% per year. Your salary will increase at 4% per year throughout your career. How much money will you have on the date of your retirement 40 years from today?
[Loan Payments] You need a 30 year, fixed-rate mortgage to buy a new home for $250,000. Your mortgage bank will lend you the money at a 5.3% APR for this 360-month loan. However, you can only afford monthly payments of $950, so you offer to pay off any remaining loan balance at the end of the loan in the form of a single balloon payment. How large will this balloon payment have to be for you to keep your monthly payments at $950?

8. [Present Value and Break-Even Interest]: Consider a firm with a contract to sell an asset for $115,000 three years from now. The asset costs $76,000 to produce today. Given a relevant discount rate on this asset of 13% per year, will the firm make a profit on this asset? At what rate does the firm just break even?

Variable Interest Rates]: A 15-year annuity pays $1,500 per month, and payments are made at the end of each month. If the interest rate is 12% compounded monthly for the first seven years, and 6% compounded monthly thereafter, what is the present value of the annuity?
[Annuities]Youaresavingforthecollegeeducationofyourtwochildren.Theyaretwoyearsapartinage;onewillbegincollege15yearsfromtodayandtheotherwillbeginl7yearsfromtoday.Youestimateyourchildrenscollegeexpensesto$45,000peryearperchild,payableatthebeginningofeachschoolyear.Theannualinterestrateis7.5%.Howmuchmoneymustyoudepositinanaccounteachyeartofundyourchildrenseducation?Yourdepositsbeginoneyearfromtoday.Youwillmakeyourlastdepositwhenyouroldestchildenterscollege.Assumefouryearsofcollege.
In excel please