Fill in the missing 17 boxes in the following spreadsheet. The spread sheet is analyzing as...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Fill in the missing 17 boxes in the following spreadsheet. The spread sheet is analyzing as adjustable rate loan for 30 years at an initial rate of 4.75% for 80% of a $450,000 property. The loan requires 1.50% points, is adjustable annually, with a 2% annual cap and 5% lifetime cap. Based on the estimated forward rates, the index to the ARM is tied is forecasted as: end of year (EOY) 1= 6.00%, EOY 2= 7.50%, EOY 3 = 8.50%. A margin of 1.50% is added to this index. The property is expected to be sold at the end of four years and there is no negative amortization. intial EOYL BOY2 EOY3 Loan Composite Amt rates used Price $450,000 % down 20% annual cap 0.02 We cap 0.05 The final column, under monthly IRR, is for calculating IRR. Years 30 1 2 3 4 5 6 9 2 952922222222 15 16 17 18 19 Balance $452.93 $359,547.07 $1,877.93 $1,877.93 $1,423.21 $454.72 $359,092.35 $1,877.93 $456.52 $358,635.82 $1,877.93 $1,877.93 $1,421.41 $1,877.93 $1,419.60 $458.33 $358,177.49 $1,877.93 $1,877.93 $1,417.79 $460.14 $357,717.35 $1,877.93 $1,877.93 7 $1,877.93 $1,415.96 $461.97 $357,255.38 $1,877.93 $1,414.14 $463.79 $356,791.59 $1,877.93 $465.63 $356,325.96 $1,877.93 $467.47 $355,858.48 $1,877.93 8 $1,877.93 $1,412.30 9 $1,877.93 $1,410.46 20 21 $1,877.93 10 $1,877.93 $1,408.61 $469.32 $355,389.16 11 $1,877.93 $1,406.75 $471.18 $354,917.98 $1,877.93 $1,877.93 $1,404.88 $473.05 $354,444.93 $1,877.93 12 13 14 23 24 26 27 28 29 Adj. Period Payment Interest Amort. $1,877.93 $1,425.00 41 42 12 30 31 32 33 34 35 36 $2,798.35 $2,534.44 37 38 39 40 points 0.0150 monthly IRR $2,798.35 $2,554.71 $243.64 $2,798.35 $2,798.35 $245.41 $349,871.85 $2,798.35 $2,798.35 $2,551.15 $247.20 $349,624.65 $2,798.35 $2,798.35 $2,549.35 $249.00 $349,375.64 $2,798.35 $2,798.35 $2,547.53 $250.82 $349,124.82 $2,798.35 $2,798.35 $2,545.70 $252.65 $348,872.17 $2,798.35 $2,798.35 $2,543.86 $254.49 $348,617.68 $2,798.35 $2,798.35 $2,542.00 $256.35 $348,361.34 $2,798.35 $2,798.35 $2,540.13 $258.22 $348,103.12 $2,798.35 $2,798.35 $2,538.25 $260.10 $347,843.02 $2,798.35 $2,798,35 $2,536.36 $262.00 $347,581.03 $2,798.35 $263.91 $347,317.12 $2,798.35 annual IRR Fill in the missing 17 boxes in the following spreadsheet. The spread sheet is analyzing as adjustable rate loan for 30 years at an initial rate of 4.75% for 80% of a $450,000 property. The loan requires 1.50% points, is adjustable annually, with a 2% annual cap and 5% lifetime cap. Based on the estimated forward rates, the index to the ARM is tied is forecasted as: end of year (EOY) 1= 6.00%, EOY 2= 7.50%, EOY 3 = 8.50%. A margin of 1.50% is added to this index. The property is expected to be sold at the end of four years and there is no negative amortization. intial EOYL BOY2 EOY3 Loan Composite Amt rates used Price $450,000 % down 20% annual cap 0.02 We cap 0.05 The final column, under monthly IRR, is for calculating IRR. Years 30 1 2 3 4 5 6 9 2 952922222222 15 16 17 18 19 Balance $452.93 $359,547.07 $1,877.93 $1,877.93 $1,423.21 $454.72 $359,092.35 $1,877.93 $456.52 $358,635.82 $1,877.93 $1,877.93 $1,421.41 $1,877.93 $1,419.60 $458.33 $358,177.49 $1,877.93 $1,877.93 $1,417.79 $460.14 $357,717.35 $1,877.93 $1,877.93 7 $1,877.93 $1,415.96 $461.97 $357,255.38 $1,877.93 $1,414.14 $463.79 $356,791.59 $1,877.93 $465.63 $356,325.96 $1,877.93 $467.47 $355,858.48 $1,877.93 8 $1,877.93 $1,412.30 9 $1,877.93 $1,410.46 20 21 $1,877.93 10 $1,877.93 $1,408.61 $469.32 $355,389.16 11 $1,877.93 $1,406.75 $471.18 $354,917.98 $1,877.93 $1,877.93 $1,404.88 $473.05 $354,444.93 $1,877.93 12 13 14 23 24 26 27 28 29 Adj. Period Payment Interest Amort. $1,877.93 $1,425.00 41 42 12 30 31 32 33 34 35 36 $2,798.35 $2,534.44 37 38 39 40 points 0.0150 monthly IRR $2,798.35 $2,554.71 $243.64 $2,798.35 $2,798.35 $245.41 $349,871.85 $2,798.35 $2,798.35 $2,551.15 $247.20 $349,624.65 $2,798.35 $2,798.35 $2,549.35 $249.00 $349,375.64 $2,798.35 $2,798.35 $2,547.53 $250.82 $349,124.82 $2,798.35 $2,798.35 $2,545.70 $252.65 $348,872.17 $2,798.35 $2,798.35 $2,543.86 $254.49 $348,617.68 $2,798.35 $2,798.35 $2,542.00 $256.35 $348,361.34 $2,798.35 $2,798.35 $2,540.13 $258.22 $348,103.12 $2,798.35 $2,798.35 $2,538.25 $260.10 $347,843.02 $2,798.35 $2,798,35 $2,536.36 $262.00 $347,581.03 $2,798.35 $263.91 $347,317.12 $2,798.35 annual IRR
Expert Answer:
Related Book For
Financial Analysis with Microsoft Excel
ISBN: 978-1285432274
7th edition
Authors: Timothy R. Mayes, Todd M. Shank
Posted Date:
Students also viewed these finance questions
-
For each problem, show your work steps. A correct answer with no work shown gets half credit (which means you fail the assignment). An incorrect answer with no work receives 0 credit. With time value...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
Using the 526 observations on workers in a certain dataset, we include educ (years of education), exper (years of labor market experience) and tenure (years with the current employer) in regression...
-
Suppose a man's scalp hair grows at a rate of 0.35 mm per day. What is this growth rate in feet per century?
-
Suppose the U.S. government defaults on its payments (i.e., cannot pay T-bills at their maturity date). What would be the effect on the T-bill rate? What would be the effect on the interest rates of...
-
The current in a cylindrical solenoid is increased smoothly from \(I_{\mathrm{i}}=0.40 \mathrm{~A}\) at \(t_{\mathrm{i}}=3.0 \mathrm{~s}\) to \(I_{\mathrm{f}}=1.20 \mathrm{~A}\) at...
-
Carmel Rugs is holding a 2-week carpet sale at Jeans Club, a local warehouse store. Carmel Rugs plans to sell carpets for $ 1,000 each. The company will purchase the carpets from a local distributor...
-
(d) For the following Boolean expression, give: i. The truth table F(a, b, c, d) = ( + b.d).(c.b.a. + .d) ii. The Karnaugh map iii. The minimal sum of products expression
-
In 2023, Lisa and Fred, a married couple, have taxable income of $400,000. If they were to file separate tax returns, Lisa would have reported taxable income of $225,000 and Fred would have reported...
-
Suppose that S is an LTI system whose exact form is unknown. It is tested by running some inputs into the system, and then observing the output signals. Suppose that the following input-output pairs...
-
The axially loaded member shown in Figure a has a cross-section as shown in Figure b. Using Fy-50 ksi, determine the design strength, fcPn, for this member. | fcPn L=20 ft | 20" Lacing 4L - 6x6x1 20"
-
Discuss the concepts of judicial accountability and judicial independence. Describe each method of selecting judges. Discuss how each of the three methods of selecting judges relates to...
-
Prepare journal entries to record the following transactions. Purchased land for $81,800 and a building for $321,800. The purchase is paid for with $61,800 cash and a note payable for $341,800. Paid...
-
Outline the purpose of an operating system (OS). Explain the interaction that takes place between an OS, applications, and computer hardware.?
-
Can digital technologies improve every part of the job of teaching in positive and productive ways? When might technology be unhelpful or even counterproductive? To explore these questions,...
-
1) Use ATMEGA16 and other software packages ATMELSTUDIO6 and PROTEUS to experimentally investigate and validate the inference with the theoretical one. With the help of the ATMEGA16 lab program write...
-
Explain briefly what is meant by electronic data interchange (EDI). How does EDI affect a companys audit trail?
-
You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in Colorado. On your first day the owner, Chuck Vitaska, told you that the...
-
You have decided to investigate a number of exchange-traded funds (ETFs) in an attempt to build a diversified portfolio. You believe that the transportation, consumer goods, energy, financials, and...
-
The Salida Salt Company is considering making a bid to supply the highway department with rock salt to drop on roads in the county during the winter. The contract will guarantee a minimum of 25,000...
-
Summer and winter constellations are different because (a) of the spin of Earth about its polar axis. (b) the night sky faces in opposite directions in summer and winter. (c) of the tilt of Earths...
-
The longest-lived stars are those of (a) low mass. (b) high mass. (c) intermediate mass. (d) infinite mass.
-
What is the star nearest Earth? (a) Proxima Centauri (b) Polaris (c) Mercury (d) the Sun
Study smarter with the SolutionInn App