Question

In: Finance

Prepare an amortization spreadsheet in Excel.  The sheet should be labeled, and I should be able to...

Prepare an amortization spreadsheet in Excel.  The sheet should be labeled, and I should be able to change purchase price, interest rate, or the other relevant factors and the spreadsheet should automatically update. As we discussed in class, spend some time labeling the spreadsheet and using proper cell references.  This is the first but not the last spreadsheet of this type, it is highly likely that elements of this spreadsheet will be helpful in subsequent assignments, so time spent here may mean less time in the future.

For an initial calculation, assume you are purchasing a $400,000 home with 5% down at an interest rate of 4%, financed over 30 years using a traditional mortgage.

Solutions

Expert Solution

Purchase price of a home 400000
Loan amount- 5% of purchase price 400000*5% 20000
amount financed 380000
Interest rate 4%
Period - Years 30
Annual payment - Using PMT function in MS excel PMT(rate,nper,pv,fv,type) rate =4% nper =30 pv =380000 fv =0 type =0 ($21,975.44)
Amortization schedule
Year beginning balance annual payment Interest = beginning balance*interest rate principal amortized = annual payment-Interest year end balance = beginning balance-principal amortized
1 380000 21975.44 15200 6775.44 373224.56
2 373224.56 21975.44 14928.9824 7046.4576 366178.1024
3 366178.1024 21975.44 14647.1241 7328.315904 358849.7865
4 358849.7865 21975.44 14353.99146 7621.44854 351228.338
5 351228.338 21975.44 14049.13352 7926.306482 343302.0315
6 343302.0315 21975.44 13732.08126 8243.358741 335058.6727
7 335058.6727 21975.44 13402.34691 8573.093091 326485.5796
8 326485.5796 21975.44 13059.42319 8916.016814 317569.5628
9 317569.5628 21975.44 12702.78251 9272.657487 308296.9053
10 308296.9053 21975.44 12331.87621 9643.563786 298653.3416
11 298653.3416 21975.44 11946.13366 10029.30634 288624.0352
12 288624.0352 21975.44 11544.96141 10430.47859 278193.5566
13 278193.5566 21975.44 11127.74227 10847.69773 267345.8589
14 267345.8589 21975.44 10693.83436 11281.60564 256064.2532
15 256064.2532 21975.44 10242.57013 11732.86987 244331.3834
16 244331.3834 21975.44 9773.255335 12202.18466 232129.1987
17 232129.1987 21975.44 9285.167948 12690.27205 219438.9267
18 219438.9267 21975.44 8777.557066 13197.88293 206241.0437
19 206241.0437 21975.44 8249.641749 13725.79825 192515.2455
20 192515.2455 21975.44 7700.609819 14274.83018 178240.4153
21 178240.4153 21975.44 7129.616612 14845.82339 163394.5919
22 163394.5919 21975.44 6535.783676 15439.65632 147954.9356
23 147954.9356 21975.44 5918.197423 16057.24258 131897.693
24 131897.693 21975.44 5275.90772 16699.53228 115198.1607
25 115198.1607 21975.44 4607.926429 17367.51357 97830.64715
26 97830.64715 21975.44 3913.225886 18062.21411 79768.43304
27 79768.43304 21975.44 3190.737322 18784.70268 60983.73036
28 60983.73036 21975.44 2439.349214 19536.09079 41447.63958
29 41447.63958 21975.44 1657.905583 20317.53442 21130.10516
30 21130.10516 21975.44 845.2042064 21130.23579 0

jeff jeffy answered 2 months ago
Next > < Previous

Related Solutions

Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and...
Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and various household expenses for 3 months. You will need to use a Totals Column or Row, and Formula to show if you are spending more money than you are bringing in or if you have a surplus! Also insert a Chart using any part of the data in your spreadsheet.
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
I need a copy of organized data in a spreadsheet with rows and columns labeled, can...
I need a copy of organized data in a spreadsheet with rows and columns labeled, can anyone help me with this please? I am doing a made up experiment where I see if eating vegetarian diets makes someone healthier. I am ‘supposed’ to find a group of people at school willing to participate and change their diet, pull their names from a jar, and randomly assign who will try the vegetarian diet and who’s diet will remain the same that...
Using Excel prepare a spreadsheet to show the profit and loss figure for the last financial...
Using Excel prepare a spreadsheet to show the profit and loss figure for the last financial year. The profit and loss should be shown as dollars and as a percentage. Enter the raw data below, applying as many presentation Features (Font, Font Size, Font Color, Number Formats and Color, Cell Shading, Text Rotation, etc) to it as you wish. Apply appropriate number formats to your numbers. Center your spreadsheet horizontally on the page Give your spreadsheet an appropriate title and...
Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare...
Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare a complete Statement of Cash Flows that shows both the direct method and the indirect method for summarizing cash flow from operating activities. Note to complete the assignment you will need to calculate items such as dividends paid (Retained Earnings & AOCI2019 – Net income2019 – Retained Earnings & AOCI2018) and capital expenditures (Net PP&E2018 – Book Value of PP&E Sold2019 – Depreciation Expense2019...
Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 6,000,000/=;...
Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 6,000,000/=; rate of interest: 12% per annum; period: 10 years payable monthly in arrears. An additional amount of Shs 500,000/= is repaid with the 20th installment, and the monthly installment recomputed to fully pay the loan balance in the initially agreed period. The rate of interest is adjusted to 13% per annum with effect from the end of the fourth year. The monthly repayment is...
1. Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs...
1. Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 4,000,000/=; rate of interest: 14% per annum; period: 10 years payable monthly in arrears. An additional amount of Shs 400,000/= is repaid with the 30th installment, and the monthly installment recomputed to fully pay the loan balance in the initially agreed period. The rate of interest is adjusted to 16% per annum with effect from the end of the fourth year. The monthly repayment...
Your task is to create the 2021 Budget in an Excel Spreadsheet.  You only need to prepare...
Your task is to create the 2021 Budget in an Excel Spreadsheet.  You only need to prepare an Income Statement Assume: The company will sell 16,000 bicycles at an average price of $2,500 each Cost of Labor is 20% of Sales Cost of Materials is 10% of Sales Variable Overhead is 6% of Sales Fixed Manufacturing Overhead is $4,000,000 Office Salaries are 5,000,000 Office Deprecation Expense is $500,000 Lease Expense of Corporate Office is $2,000,000 Advertising is $1,000,000 Income Taxes are...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,175. The bonds may be called in 5 years at...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,185. The bonds may be called in 5 years at...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT