Question
|
Ensure that the Facilities worksheet is active. Use Goal Seek to
reduce the monthly payment in cell B6 to the optimal value of
$6000. Complete this task by changing the Loan
amount in cell E6. |
Homework Answers
Answer #1
Facility Amortization Table calculation:
| Facility Amortization Table | |||||||
| Payment details | Loan Details | ||||||
| Payment | $6,000 | Loan | $312,227.32 | ||||
| APR | 5.75% | Periodic rate | 0.479% | ||||
| Years | 5 | Number of periods | 60 | ||||
| PMTs per year | 12 | ||||||
| Payment Number | Beginning Balance | Payment amount | Interest paid | Principal repayment | Remaining balance | Cumulative interest | Cumulative Principal |
| 1 | $ 312,227.32 | $6,000 | $ 1,496.09 | $4,503.91 | $ 307,723.41 | $ 1,496.09 | $4,503.91 |
| 2 | $ 307,723.41 | $6,000 | $ 1,474.51 | $4,525.49 | $ 303,197.92 | $ 2,970.60 | $9,029.40 |
| 3 | $ 303,197.92 | $6,000 | $ 1,452.82 | $4,547.18 | $ 298,650.74 | $ 4,423.42 | $13,576.58 |
| 4 | $ 298,650.74 | $6,000 | $ 1,431.03 | $4,568.97 | $ 294,081.77 | $ 5,854.46 | $18,145.54 |
| 5 | $ 294,081.77 | $6,000 | $ 1,409.14 | $4,590.86 | $ 289,490.92 | $ 7,263.60 | $22,736.40 |
| 6 | $ 289,490.92 | $6,000 | $ 1,387.14 | $4,612.86 | $ 284,878.06 | $ 8,650.74 | $27,349.26 |
| 7 | $ 284,878.06 | $6,000 | $ 1,365.04 | $4,634.96 | $ 280,243.10 | $ 10,015.78 | $31,984.22 |
| 8 | $ 280,243.10 | $6,000 | $ 1,342.83 | $4,657.17 | $ 275,585.93 | $ 11,358.61 | $36,641.39 |
| 9 | $ 275,585.93 | $6,000 | $ 1,320.52 | $4,679.48 | $ 270,906.45 | $ 12,679.13 | $41,320.87 |
| 10 | $ 270,906.45 | $6,000 | $ 1,298.09 | $4,701.91 | $ 266,204.54 | $ 13,977.22 | $46,022.78 |
| 11 | $ 266,204.54 | $6,000 | $ 1,275.56 | $4,724.44 | $ 261,480.10 | $ 15,252.79 | $50,747.21 |
| 12 | $ 261,480.10 | $6,000 | $ 1,252.93 | $4,747.07 | $ 256,733.03 | $ 16,505.71 | $55,494.29 |
| 13 | $ 256,733.03 | $6,000 | $ 1,230.18 | $4,769.82 | $ 251,963.21 | $ 17,735.89 | $60,264.11 |
| 14 | $ 251,963.21 | $6,000 | $ 1,207.32 | $4,792.68 | $ 247,170.53 | $ 18,943.21 | $65,056.79 |
| 15 | $ 247,170.53 | $6,000 | $ 1,184.36 | $4,815.64 | $ 242,354.89 | $ 20,127.57 | $69,872.43 |
| 16 | $ 242,354.89 | $6,000 | $ 1,161.28 | $4,838.72 | $ 237,516.18 | $ 21,288.86 | $74,711.14 |
| 17 | $ 237,516.18 | $6,000 | $ 1,138.10 | $4,861.90 | $ 232,654.27 | $ 22,426.96 | $79,573.04 |
| 18 | $ 232,654.27 | $6,000 | $ 1,114.80 | $4,885.20 | $ 227,769.08 | $ 23,541.76 | $84,458.24 |
| 19 | $ 227,769.08 | $6,000 | $ 1,091.39 | $4,908.61 | $ 222,860.47 | $ 24,633.15 | $89,366.85 |
| 20 | $ 222,860.47 | $6,000 | $ 1,067.87 | $4,932.13 | $ 217,928.34 | $ 25,701.02 | $94,298.98 |
| 21 | $ 217,928.34 | $6,000 | $ 1,044.24 | $4,955.76 | $ 212,972.58 | $ 26,745.26 | $99,254.74 |
| 22 | $ 212,972.58 | $6,000 | $ 1,020.49 | $4,979.51 | $ 207,993.08 | $ 27,765.76 | $104,234.24 |
| 23 | $ 207,993.08 | $6,000 | $ 996.63 | $5,003.37 | $ 202,989.71 | $ 28,762.39 | $109,237.61 |
| 24 | $ 202,989.71 | $6,000 | $ 972.66 | $5,027.34 | $ 197,962.37 | $ 29,735.05 | $114,264.95 |
| 25 | $ 197,962.37 | $6,000 | $ 948.57 | $5,051.43 | $ 192,910.94 | $ 30,683.62 | $119,316.38 |
| 26 | $ 192,910.94 | $6,000 | $ 924.36 | $5,075.64 | $ 187,835.30 | $ 31,607.98 | $124,392.02 |
| 27 | $ 187,835.30 | $6,000 | $ 900.04 | $5,099.96 | $ 182,735.35 | $ 32,508.03 | $129,491.97 |
| 28 | $ 182,735.35 | $6,000 | $ 875.61 | $5,124.39 | $ 177,610.95 | $ 33,383.64 | $134,616.36 |
| 29 | $ 177,610.95 | $6,000 | $ 851.05 | $5,148.95 | $ 172,462.01 | $ 34,234.69 | $139,765.31 |
| 30 | $ 172,462.01 | $6,000 | $ 826.38 | $5,173.62 | $ 167,288.39 | $ 35,061.07 | $144,938.93 |
| 31 | $ 167,288.39 | $6,000 | $ 801.59 | $5,198.41 | $ 162,089.98 | $ 35,862.66 | $150,137.34 |
| 32 | $ 162,089.98 | $6,000 | $ 776.68 | $5,223.32 | $ 156,866.66 | $ 36,639.34 | $155,360.66 |
| 33 | $ 156,866.66 | $6,000 | $ 751.65 | $5,248.35 | $ 151,618.31 | $ 37,390.99 | $160,609.01 |
| 34 | $ 151,618.31 | $6,000 | $ 726.50 | $5,273.50 | $ 146,344.81 | $ 38,117.50 | $165,882.50 |
| 35 | $ 146,344.81 | $6,000 | $ 701.24 | $5,298.76 | $ 141,046.05 | $ 38,818.73 | $171,181.27 |
| 36 | $ 141,046.05 | $6,000 | $ 675.85 | $5,324.15 | $ 135,721.90 | $ 39,494.58 | $176,505.42 |
| 37 | $ 135,721.90 | $6,000 | $ 650.33 | $5,349.67 | $ 130,372.23 | $ 40,144.91 | $181,855.09 |
| 38 | $ 130,372.23 | $6,000 | $ 624.70 | $5,375.30 | $ 124,996.93 | $ 40,769.61 | $187,230.39 |
| 39 | $ 124,996.93 | $6,000 | $ 598.94 | $5,401.06 | $ 119,595.87 | $ 41,368.56 | $192,631.44 |
| 40 | $ 119,595.87 | $6,000 | $ 573.06 | $5,426.94 | $ 114,168.94 | $ 41,941.62 | $198,058.38 |
| 41 | $ 114,168.94 | $6,000 | $ 547.06 | $5,452.94 | $ 108,716.00 | $ 42,488.68 | $203,511.32 |
| 42 | $ 108,716.00 | $6,000 | $ 520.93 | $5,479.07 | $ 103,236.93 | $ 43,009.61 | $208,990.39 |
| 43 | $ 103,236.93 | $6,000 | $ 494.68 | $5,505.32 | $ 97,731.60 | $ 43,504.29 | $214,495.71 |
| 44 | $ 97,731.60 | $6,000 | $ 468.30 | $5,531.70 | $ 92,199.90 | $ 43,972.58 | $220,027.42 |
| 45 | $ 92,199.90 | $6,000 | $ 441.79 | $5,558.21 | $ 86,641.69 | $ 44,414.38 | $225,585.62 |
| 46 | $ 86,641.69 | $6,000 | $ 415.16 | $5,584.84 | $ 81,056.85 | $ 44,829.53 | $231,170.47 |
| 47 | $ 81,056.85 | $6,000 | $ 388.40 | $5,611.60 | $ 75,445.25 | $ 45,217.93 | $236,782.07 |
| 48 | $ 75,445.25 | $6,000 | $ 361.51 | $5,638.49 | $ 69,806.76 | $ 45,579.44 | $242,420.56 |
| 49 | $ 69,806.76 | $6,000 | $ 334.49 | $5,665.51 | $ 64,141.25 | $ 45,913.93 | $248,086.07 |
| 50 | $ 64,141.25 | $6,000 | $ 307.34 | $5,692.66 | $ 58,448.59 | $ 46,221.27 | $253,778.73 |
| 51 | $ 58,448.59 | $6,000 | $ 280.07 | $5,719.93 | $ 52,728.66 | $ 46,501.34 | $259,498.66 |
| 52 | $ 52,728.66 | $6,000 | $ 252.66 | $5,747.34 | $ 46,981.32 | $ 46,754.00 | $265,246.00 |
| 53 | $ 46,981.32 | $6,000 | $ 225.12 | $5,774.88 | $ 41,206.43 | $ 46,979.12 | $271,020.88 |
| 54 | $ 41,206.43 | $6,000 | $ 197.45 | $5,802.55 | $ 35,403.88 | $ 47,176.56 | $276,823.44 |
| 55 | $ 35,403.88 | $6,000 | $ 169.64 | $5,830.36 | $ 29,573.53 | $ 47,346.21 | $282,653.79 |
| 56 | $ 29,573.53 | $6,000 | $ 141.71 | $5,858.29 | $ 23,715.23 | $ 47,487.91 | $288,512.09 |
| 57 | $ 23,715.23 | $6,000 | $ 113.64 | $5,886.36 | $ 17,828.87 | $ 47,601.55 | $294,398.45 |
| 58 | $ 17,828.87 | $6,000 | $ 85.43 | $5,914.57 | $ 11,914.30 | $ 47,686.98 | $300,313.02 |
| 59 | $ 11,914.30 | $6,000 | $ 57.09 | $5,942.91 | $ 5,971.39 | $ 47,744.07 | $306,255.93 |
| 60 | $ 5,971.39 | $6,000 | $ 28.61 | $5,971.39 | $ 0.00 | $ 47,772.68 | $312,227.32 |
Formulas used in excel calculation:
Facility Amortization Table t details Loan Details PV(E7,E8,-B6,0,0) 6000 Loan 7 APR 0.0575 Periodic rate Number of periods 8 Years 0 9 PMTs 11 Payment Number inning Balance Pa ent amount Interest paid Remaining balance Cumulative interest Cumulative Principal 312227.318047975 6000 7 2 6 2 7
Know the answer?
Add Answer to:
Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in...
Not the answer you're looking for?
Ask your own homework help question.
Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in...
Ensure that the Facilities worksheet is
active. Use Goal Seek to reduce the monthly payment in
cell B6 to the optimal value of
$6000. Complete this task by changing the Loan
amount in cell E6.
Create the following three scenarios using Scenario Manager.
The scenarios should change the cells B7, B8, and
E6.
Good
B7 = .0325
B8 =
5
E6 =
275000
Most Likely
B7 =
.0575
B8 =
5
E6 = 312227.32
Bad
B7 = .0700 B8 =...
You have recently become the CFO for Beta Manufacturing, a small cap company that produces auto parts
EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.6 Project Description:You have recently become the CFO for Beta Manufacturing, a small cap company that produces auto parts. As you step into your new position, you have decided to compile a report that details all aspects of the business, including: employee tax withholding, facility management, sales data, and product inventory. To complete the task, you will duplicate existing formatting, utilize various conditional logic functions, complete an amortization table with financial functions, visualize data with PivotTables, and lastly...
Please show the steps BoyuQuCh7CaseStudy - Excel File Insert Page Layout Formulas Data Review View Add-ins...
Please show the steps
BoyuQuCh7CaseStudy - Excel File Insert Page Layout Formulas Data Review View Add-ins ACROBAT QuickBooks Tell me what you want to do Sign in Share Σ Autosum Calibri Fill Paste в ㅣ u . re. O . . _ Ξ_ 트트 분 Merge & Center. $. % , 'i..g Conditional Format as Cell Insert Delete Format Sort & Find & Filter Select Formatting Table Styles Clipboard Font Alignment Number Cells Editing E3 Input Area Calculations 2 Facility...
Ensure that the Facilities worksheet is
active. Use Goal Seek to reduce the monthly payment in
cell B6 to the optimal value of
$6000. Complete this task by changing the Loan
amount in cell E6.
Create the following three scenarios using Scenario Manager.
The scenarios should change the cells B7, B8, and
E6.
Good
B7 = .0325
B8 =
5
E6 =
275000
Most Likely
B7 =
.0575
B8 =
5
E6 = 312227.32
Bad
B7 = .0700 B8 =...
Please show the steps
BoyuQuCh7CaseStudy - Excel File Insert Page Layout Formulas Data Review View Add-ins ACROBAT QuickBooks Tell me what you want to do Sign in Share Σ Autosum Calibri Fill Paste в ㅣ u . re. O . . _ Ξ_ 트트 분 Merge & Center. $. % , 'i..g Conditional Format as Cell Insert Delete Format Sort & Find & Filter Select Formatting Table Styles Clipboard Font Alignment Number Cells Editing E3 Input Area Calculations 2 Facility...
ADVERTISEMENT
Need Online Homework Help?
Ask
a QuestionGet Answers For Free
Most questions answered within 3 hours.
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
Active Questions
-
4. Without doing any calculations, predict whether the observed
∆T would increase, decrease or remain the...
asked 4 minutes ago -
Based on the range, which of the following sets of scores has
the greatest variability? 3,...
asked 1 hour ago -
Ripples in a pond travel at a velocity of 3 m/s with one peak
passing a...
asked 1 hour ago -
A man stands on the roof of a building of height 13.0 mm and
throws a...
asked 1 hour ago -
The extent to which assets are financed by borrowed funds and
other liabilities is indicated by:...
asked 2 hours ago -
Explain in detail
Germany is the fifth largest economy
explain what goods and services Germany specializes...
asked 2 hours ago -
The density of platinum is 21.45 g/mL. If a cube of platinum
with a mass of...
asked 2 hours ago -
Accounts Receivable
Sales
A/R Posting
Extended Sales Invoice
Packing Slip
Compare invoice to packing slip 2...
asked 2 hours ago -
Michaella, age 23, is a full-time law student and is claimed by
her parents as a...
asked 2 hours ago -
Why are polymers not typically casted into products?
asked 2 hours ago -
When rolling a die 129 times, what is the probability of rolling
a 6 no more...
asked 3 hours ago -
4. A call option currently sells for $7.75. It has a strike
price of $85 and...
asked 2 hours ago
ADVERTISEMENT