Your mom will lend you money so long as you agree to pay her back within five years and you offer to pay her the rate of interest that she would otherwise get by putting his money in a saving account. Based on your earnings and living expenses, you think you will be able to pay him 8000$ in one year and then 10000$ each year for the following next four years. If your mom would otherwise earn 5% per year on her savings how much you can borrow from her?

Your mom will lend you money so long as you agree to pay her back within five years and you offer to pay her the rate of interest that she would otherwise get by putting his money in a saving account. Based on your earnings and living expenses, you think you will be able to pay him 8000$ in one year and then 10000$ each year for the following next four years. If your mom would otherwise earn 5% per year on her savings how much you can borrow from her?

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 36P

See similar textbooks

Similar questions

Suppose you currently have a portfolio of three stocks, A, B, and C. You own 500 shares of A, 300 of B, and 1000 of C. The current share prices are 42.76, 81.33, and, 58.22, respectively. You plan to hold this portfolio for at least a year. During the coming year, economists have predicted that the national economy will be awful, stable, or great with probabilities 0.2, 0.5, and 0.3. Given the state of the economy, the returns (one-year percentage changes) of the three stocks are independent and normally distributed. However, the means and standard deviations of these returns depend on the state of the economy, as indicated in the file P11_23.xlsx. a. Use @RISK to simulate the value of the portfolio and the portfolio return in the next year. How likely is it that you will have a negative return? How likely is it that you will have a return of at least 25%? b. Suppose you had a crystal ball where you could predict the state of the economy with certainty. The stock returns would still be uncertain, but you would know whether your means and standard deviations come from row 6, 7, or 8 of the P11_23.xlsx file. If you learn, with certainty, that the economy is going to be great in the next year, run the appropriate simulation to answer the same questions as in part a. Repeat this if you learn that the economy is going to be awful. How do these results compare with those in part a?
Suppose you begin year 1 with 5000. At the beginning of each year, you put half of your money under a mattress and invest the other half in Whitewater stock. During each year, there is a 40% chance that the Whitewater stock will double, and there is a 60% chance that you will lose half of your investment. To illustrate, if the stock doubles during the first year, you will have 3750 under the mattress and 3750 invested in Whitewater during year 2. You want to estimate your annual return over a 30-year period. If you end with F dollars, your annual return is (F/5000)1/30 1. For example, if you end with 100,000, your annual return is 201/30 1 = 0.105, or 10.5%. Run 1000 replications of an appropriate simulation. Based on the results, you can be 95% certain that your annual return will be between which two values?
A project does not necessarily have a unique IRR. (Refer to the previous problem for more information on IRR.) Show that a project with the following cash flows has two IRRs: year 1, 20; year 2, 82; year 3, 60; year 4, 2. (Note: It can be shown that if the cash flow of a project changes sign only once, the project is guaranteed to have a unique IRR.)
The IRR is the discount rate r that makes a project have an NPV of 0. You can find IRR in Excel with the built-in IRR function, using the syntax =IRR(range of cash flows). However, it can be tricky. In fact, if the IRR is not near 10%, this function might not find an answer, and you would get an error message. Then you must try the syntax =IRR(range of cash flows, guess), where guess" is your best guess for the IRR. It is best to try a range of guesses (say, 90% to 100%). Find the IRR of the project described in Problem 34. 34. Consider a project with the following cash flows: year 1, 400; year 2, 200; year 3, 600; year 4, 900; year 5, 1000; year 6, 250; year 7, 230. Assume a discount rate of 15% per year. a. Find the projects NPV if cash flows occur at the ends of the respective years. b. Find the projects NPV if cash flows occur at the beginnings of the respective years. c. Find the projects NPV if cash flows occur at the middles of the respective years.
You are considering a 10-year investment project. At present, the expected cash flow each year is 10,000. Suppose, however, that each years cash flow is normally distributed with mean equal to last years actual cash flow and standard deviation 1000. For example, suppose that the actual cash flow in year 1 is 12,000. Then year 2 cash flow is normal with mean 12,000 and standard deviation 1000. Also, at the end of year 1, your best guess is that each later years expected cash flow will be 12,000. a. Estimate the mean and standard deviation of the NPV of this project. Assume that cash flows are discounted at a rate of 10% per year. b. Now assume that the project has an abandonment option. At the end of each year you can abandon the project for the value given in the file P11_60.xlsx. For example, suppose that year 1 cash flow is 4000. Then at the end of year 1, you expect cash flow for each remaining year to be 4000. This has an NPV of less than 62,000, so you should abandon the project and collect 62,000 at the end of year 1. Estimate the mean and standard deviation of the project with the abandonment option. How much would you pay for the abandonment option? (Hint: You can abandon a project at most once. So in year 5, for example, you abandon only if the sum of future expected NPVs is less than the year 5 abandonment value and the project has not yet been abandoned. Also, once you abandon the project, the actual cash flows for future years are zero. So in this case the future cash flows after abandonment should be zero in your model.)
A European put option allows an investor to sell a share of stock at the exercise price on the exercise data. For example, if the exercise price is 48, and the stock price is 45 on the exercise date, the investor can sell the stock for 48 and then immediately buy it back (that is, cover his position) for 45, making 3 profit. But if the stock price on the exercise date is greater than the exercise price, the option is worthless at that date. So for a put, the investor is hoping that the price of the stock decreases. Using the same parameters as in Example 11.7, find a fair price for a European put option. (Note: As discussed in the text, an actual put option is usually for 100 shares.)
Rerun the new car simulation from Example 11.4, but now use the RISKSIMTABLE function appropriately to simulate discount rates of 5%, 7.5%, 10%, 12.5%, and 15%. Comment on how the outputs change as the discount rate decreases from the value used in the example, 10%.
Based on Kelly (1956). You currently have 100. Each week you can invest any amount of money you currently have in a risky investment. With probability 0.4, the amount you invest is tripled (e.g., if you invest 100, you increase your asset position by 300), and, with probability 0.6, the amount you invest is lost. Consider the following investment strategies: Each week, invest 10% of your money. Each week, invest 30% of your money. Each week, invest 50% of your money. Use @RISK to simulate 100 weeks of each strategy 1000 times. Which strategy appears to be best in terms of the maximum growth rate? (In general, if you can multiply your investment by M with probability p and lose your investment with probability q = 1 p, you should invest a fraction [p(M 1) q]/(M 1) of your money each week. This strategy maximizes the expected growth rate of your fortune and is known as the Kelly criterion.) (Hint: If an initial wealth of I dollars grows to F dollars in 100 weeks, the weekly growth rate, labeled r, satisfies F = (I + r)100, so that r = (F/I)1/100 1.)
Amanda has 30 years to save for her retirement. At the beginning of each year, she puts 5000 into her retirement account. At any point in time, all of Amandas retirement funds are tied up in the stock market. Suppose the annual return on stocks follows a normal distribution with mean 12% and standard deviation 25%. What is the probability that at the end of 30 years, Amanda will have reached her goal of having 1,000,000 for retirement? Assume that if Amanda reaches her goal before 30 years, she will stop investing. (Hint: Each year you should keep track of Amandas beginning cash positionfor year 1, this is 5000and Amandas ending cash position. Of course, Amandas ending cash position for a given year is a function of her beginning cash position and the return on stocks for that year. To estimate the probability that Amanda meets her goal, use an IF statement that returns 1 if she meets her goal and 0 otherwise.)
It is January 1 of year 0, and Merck is trying to determine whether to continue development of a new drug. The following information is relevant. You can assume that all cash flows occur at the ends of the respective years. Clinical trials (the trials where the drug is tested on humans) are equally likely to be completed in year 1 or 2. There is an 80% chance that clinical trials will succeed. If these trials fail, the FDA will not allow the drug to be marketed. The cost of clinical trials is assumed to follow a triangular distribution with best case 100 million, most likely case 150 million, and worst case 250 million. Clinical trial costs are incurred at the end of the year clinical trials are completed. If clinical trials succeed, the drug will be sold for five years, earning a profit of 6 per unit sold. If clinical trials succeed, a plant will be built during the same year trials are completed. The cost of the plant is assumed to follow a triangular distribution with best case 1 billion, most likely case 1.5 billion, and worst case 2.5 billion. The plant cost will be depreciated on a straight-line basis during the five years of sales. Sales begin the year after successful clinical trials. Of course, if the clinical trials fail, there are no sales. During the first year of sales, Merck believe sales will be between 100 million and 200 million units. Sales of 140 million units are assumed to be three times as likely as sales of 120 million units, and sales of 160 million units are assumed to be twice as likely as sales of 120 million units. Merck assumes that for years 2 to 5 that the drug is on the market, the growth rate will be the same each year. The annual growth in sales will be between 5% and 15%. There is a 25% chance that the annual growth will be 7% or less, a 50% chance that it will be 9% or less, and a 75% chance that it will be 12% or less. Cash flows are discounted 15% per year, and the tax rate is 40%. Use simulation to model Mercks situation. Based on the simulation output, would you recommend that Merck continue developing? Explain your reasoning. What are the three key drivers of the projects NPV? (Hint: The way the uncertainty about the first year sales is stated suggests using the General distribution, implemented with the RISKGENERAL function. Similarly, the way the uncertainty about the annual growth rate is stated suggests using the Cumul distribution, implemented with the RISKCUMUL function. Look these functions up in @RISKs online help.)
If you want to replicate the results of a simulation model with Excel functions only, not @RISK, you can build a data table and let the column input cell be any blank cell. Explain why this works.
In August of the current year, a car dealer is trying to determine how many cars of the next model year to order. Each car ordered in August costs 20,000. The demand for the dealers next year models has the probability distribution shown in the file P10_12.xlsx. Each car sells for 25,000. If demand for next years cars exceeds the number of cars ordered in August, the dealer must reorder at a cost of 22,000 per car. Excess cars can be disposed of at 17,000 per car. Use simulation to determine how many cars to order in August. For your optimal order quantity, find a 95% confidence interval for the expected profit.