Objective: Make an amortization table for 30 year fixed rate mortgage. The table should be a model. A model implies that the spreadsheet should adapt to different input values automatically and offer...

1 answer below ยป


Objective:Make an amortization table for 30 year fixed rate mortgage. The table should be a model. A model implies that the spreadsheet should adapt to different input values automatically and offer correct answers under different assumed inputs. The model should use varied inputs as defined below. The spreadsheet should be very simple to navigate. Any person with little knowledge of spreadsheets should be able to use this model.




Grading Policy:The model will be evaluated for accuracy and adaptability with different assumed inputs. The total assignment grade is 20 points from the homework section of course grade. Extra points may be awarded for making your model look smart and sharp, however it is not a basic requirement.You must use the financial functions of Excel.



Students may complete this assignment individually.Paper submissions are nor accepted. Please submit your assignment in Canvas under assignment upload.




Due Date: May 5th 2021:The assignment may be submitted any time before the due date. Please do not send any requests for extensions. If the assignment is not received by the deadline a zero grade will be awarded automatically. Please do not ask for extensions as it will not be awarded.




Suggestions:Create an input section where the user may input numbers. Leave sufficient instructions for the user. (for example if the rate must be entered as a decimal or a percentage, is this the annual rate or does the user have to divide the rate by 12, should the term be entered as years or if the user has to find number of months etc.)



Create an output section, preferably on the next TAB available at the bottom. Name the TAB as INPUT and OUTPUT so the user can identify.



INPUTS: Suggested inputs maybe (but not limited to) Value of the property, Down payment (Percentage); Value of the loan, Term, Estimated time until reversion, Financing rate, Points, Prepayment penalties, Other closing costs, Total closing costs, Assessed Value, Property Tax rate per $1,000 of assessed value, monthly insurance (insurance and property taxes will go to the escrow payment to the lender monthly), P1, P2, etc.


The input section may contain some values that will be automatically computed based on your inputs and no input is really required. These values may be: Payment, Estimated reversion value, dollar amount of points, dollar amount of prepayment penalties, dollar amount of down payment, reversion value, etc.



OUTPUTS: The output section will contain a one-line amortization table first with columns such as P1, P2, Beg Balance, Pmt, Interest, Principal, End Balance.


Next you may provide some other output relevant to the user such as Effective rate of borrowing, APR (This might be a little hard and you may have to use IF statement nested)


Next the amortization table will be constructed for 30 years, monthly payments. Suggested columns included may be Month, Begin Balance, Loan Payment, Interest, Principal amortization, End Balance, Escrow Payment (property taxes and insurance), Total payment (loan plus escrow).


The values in the table should change as the input values are changed. So please try out your output with different values. Use PV or FV function for Balance column. Format your display to be centered and two places after decimal.


Try the short cuts rather than writing formulas in all 360 rows. Explore the help function of excel, to learn about copying cells, absolute and relative cell references. Use your imagination



Extras: Use IF statements to hide the cells if the term is less than 360 months. Put nice borders and colors to make the spreadsheet look pleasing to eyes, put embedded comments in the cells for the user with instructions. If you are more adventurous, you may explore how to automate functions using macros.


Use Excel Functions

sydney-amortization-table-real-estate-uwiqriwb-yjnhxg2n.xlsx
Answered Same DayMay 05, 2021

Answer To: Objective: Make an amortization table for 30 year fixed rate mortgage. The table should be a model....

Neenisha answered on May 06 2021
149 Votes
INPUTS
    INPUTS
    Property Value    $ 3,500,000
    Down Payment(%)    25%
    Value of Loan    $ 875,000
    Closing Cost(%)    3.50%
    Total Closing costs    $ 122,500
    Years    30
    Annual Interest Rate (%)    8%    per annum
    Assessed Value of Property (%)    60%
    Total Assessed Value of Property    $ 2,100,000
    Property Tax Rate (Per $1000 assessed value)    10%    per annum
    Property Tax Per Year    $ 21,000
    Monthly Property Tax    $ 1,750
    Insurance Rate    0.50%    per annum
    Total Insurance Per Year    $ 10,500
    Monthly Insurance    $ 875
OUTPUTS
    Amount Borrowed (Value of Loan)        $ 875,000        APR    0.67%
    Periods (No of Years*12)        360        Effective Rate of Borrowing    8.30%
    Rate (p.a.)        8.00%
    Payment        $ 6,420
    Months    Beginning    PMT    Interest    Principal     Ending Balance    Escrow Payment    Total Payment
    1    $ 875,000.00    $ 6,420.44    $ 5,833.33    $ 587.11    $ 874,412.89    $ 2,625.00    $ 9,045.44
    2    $ 874,412.89    $ 6,420.44    $ 5,829.42    $ 591.02    $ 873,821.87    $ 2,625.00    $ 9,045.44
    3    $ 873,821.87    $ 6,420.44    $ 5,825.48    $ 594.96    $ 873,226.91    $ 2,625.00    $ 9,045.44
    4    $ 873,226.91    $ 6,420.44    $ 5,821.51    $ 598.93    $ 872,627.98    $ 2,625.00    $ 9,045.44
    5    $ 872,627.98    $ 6,420.44    $ 5,817.52    $ 602.92    $ 872,025.06    $ 2,625.00    $ 9,045.44
    6    $ 872,025.06    $ 6,420.44    $ 5,813.50    $ 606.94    $ 871,418.12    $ 2,625.00    $ 9,045.44
    7    $ 871,418.12    $ 6,420.44    $ 5,809.45    $ 610.99    $ 870,807.14    $ 2,625.00    $ 9,045.44
    8    $ 870,807.14    $ 6,420.44    $ 5,805.38    $ 615.06    $ 870,192.08    $ 2,625.00    $ 9,045.44
    9    $ 870,192.08    $ 6,420.44    $ 5,801.28    $ 619.16    $ 869,572.92    $ 2,625.00    $ 9,045.44
    10    $ 869,572.92    $ 6,420.44    $ 5,797.15    $ 623.29    $ 868,949.63    $ 2,625.00    $ 9,045.44
    11    $ 868,949.63    $ 6,420.44    $ 5,793.00    $ 627.44    $ 868,322.19    $ 2,625.00    $ 9,045.44
    12    $ 868,322.19    $ 6,420.44    $ 5,788.81    $ 631.63    $ 867,690.57    $ 2,625.00    $ 9,045.44
    13    $ 867,690.57    $ 6,420.44    $ 5,784.60    $ 635.84    $ 867,054.73    $ 2,625.00    $ 9,045.44
    14    $ 867,054.73    $ 6,420.44    $ 5,780.36    $ 640.08    $ 866,414.65    $ 2,625.00    $ 9,045.44
    15    $ 866,414.65    $ 6,420.44    $ 5,776.10    $ 644.34    $ 865,770.31    $ 2,625.00    $ 9,045.44
    16    $ 865,770.31    $ 6,420.44    $ 5,771.80    $ 648.64    $ 865,121.67    $ 2,625.00    $ 9,045.44
    17    $ 865,121.67    $ 6,420.44    $ 5,767.48    $ 652.96    $ 864,468.71    $ 2,625.00    $ 9,045.44
    18    $ 864,468.71    $ 6,420.44    $ 5,763.12    $ 657.32    $ 863,811.40    $ 2,625.00    $ 9,045.44
    19    $ 863,811.40    $ 6,420.44    $ 5,758.74    $ 661.70    $ 863,149.70    $ 2,625.00    $ 9,045.44
    20    $ 863,149.70    $ 6,420.44    $ 5,754.33    $ 666.11    $ 862,483.59    $ 2,625.00    $ 9,045.44
    21    $ 862,483.59    $ 6,420.44    $ 5,749.89    $ 670.55    $ 861,813.04    $ 2,625.00    $ 9,045.44
    22    $ 861,813.04    $ 6,420.44    $ 5,745.42    $ 675.02    $ 861,138.02    $ 2,625.00    $ 9,045.44
    23    $ 861,138.02    $ 6,420.44    $ 5,740.92    $ 679.52    $ 860,458.50    $ 2,625.00    $ 9,045.44
    24    $ 860,458.50    $ 6,420.44    $ 5,736.39    $ 684.05    $ 859,774.45    $ 2,625.00    $ 9,045.44
    25    $ 859,774.45    $ 6,420.44    $ 5,731.83    $ 688.61    $ 859,085.84    $ 2,625.00    $ 9,045.44
    26    $ 859,085.84    $ 6,420.44    $ 5,727.24    $ 693.20    $ 858,392.64    $ 2,625.00    $ 9,045.44
    27    $ 858,392.64    $ 6,420.44    $ 5,722.62    $ 697.82    $ 857,694.82    $ 2,625.00    $ 9,045.44
    28    $ 857,694.82    $ 6,420.44    $ 5,717.97    $ 702.47    $ 856,992.34    $ 2,625.00    $ 9,045.44
    29    $ 856,992.34    $ 6,420.44    $ 5,713.28    $ 707.16    $ 856,285.18    $ 2,625.00    $ 9,045.44
    30    $ 856,285.18    $ 6,420.44    $ 5,708.57    $ 711.87    $ 855,573.31    $ 2,625.00    $ 9,045.44
    31    $ 855,573.31    $ 6,420.44    $ 5,703.82    $ 716.62    $ 854,856.69    $ 2,625.00    $ 9,045.44
    32    $ 854,856.69    $ 6,420.44    $ 5,699.04    $ 721.40    $ 854,135.30    $ 2,625.00    $ 9,045.44
    33    $ 854,135.30    $ 6,420.44    $ 5,694.24    $ 726.20    $ 853,409.09    $ 2,625.00    $ 9,045.44
    34    $ 853,409.09    $ 6,420.44    $ 5,689.39    $ 731.05    $ 852,678.05    $ 2,625.00    $ 9,045.44
    35    $ 852,678.05    $ 6,420.44    $ 5,684.52    $ 735.92    $ 851,942.13    $ 2,625.00    $ 9,045.44
    36    $ 851,942.13    $ 6,420.44    $ 5,679.61    $ 740.83    $ 851,201.30    $ 2,625.00    $ 9,045.44
    37    $ 851,201.30    $ 6,420.44    $ 5,674.68    $ 745.76    $ 850,455.54    $ 2,625.00    $ 9,045.44
    38    $ 850,455.54    $ 6,420.44    $ 5,669.70    $ 750.74    $ 849,704.80    $ 2,625.00    $ 9,045.44
    39    $ 849,704.80    $ 6,420.44    $ 5,664.70    $ 755.74    $ 848,949.06    $ 2,625.00    $ 9,045.44
    40    $ 848,949.06    $ 6,420.44    $ 5,659.66    $ 760.78    $ 848,188.28    $ 2,625.00    $ 9,045.44
    41    $ 848,188.28    $ 6,420.44    $ 5,654.59    $ 765.85    $ 847,422.43    $ 2,625.00    $ 9,045.44
    42    $ 847,422.43    $ 6,420.44    $ 5,649.48    $ 770.96    $ 846,651.47    $ 2,625.00    $ 9,045.44
    43    $ 846,651.47    $ 6,420.44    $ 5,644.34    $ 776.10    $ 845,875.38    $ 2,625.00    $ 9,045.44
    44    $ 845,875.38    $ 6,420.44    $ 5,639.17    $ 781.27    $ 845,094.10    $ 2,625.00    $ 9,045.44
    45    $ 845,094.10    $ 6,420.44    $ 5,633.96    $ 786.48    $ 844,307.63    $ 2,625.00    $ 9,045.44
    46    $ 844,307.63    $ 6,420.44    $ 5,628.72    $ 791.72    $ 843,515.90    $ 2,625.00    $ 9,045.44
    47    $ 843,515.90    $ 6,420.44    $ 5,623.44    $ 797.00    $ 842,718.90    $ 2,625.00    $ 9,045.44
    48    $ 842,718.90    $ 6,420.44    $ 5,618.13    $ 802.31    $ 841,916.59    $ 2,625.00    $ 9,045.44
    49    $ 841,916.59    $ 6,420.44    $ 5,612.78    $ 807.66    $ 841,108.93    $ 2,625.00    $ 9,045.44
    50    $ 841,108.93    $ 6,420.44    $ 5,607.39    $ 813.05    $ 840,295.88    $ 2,625.00    $ 9,045.44
    51    $ 840,295.88    $ 6,420.44    $ 5,601.97    $ 818.47    $ 839,477.41    $ 2,625.00    $ 9,045.44
    52    $ 839,477.41    $ 6,420.44    $ 5,596.52    $ 823.92    $ 838,653.49    $ 2,625.00    $ 9,045.44
    53    $ 838,653.49    $ 6,420.44    $ 5,591.02    $ 829.42    $ 837,824.07    $ 2,625.00    $ 9,045.44
    54    $ 837,824.07    $ 6,420.44    $ 5,585.49    $ 834.95    $ 836,989.12    $ 2,625.00    $ 9,045.44
    55    $ 836,989.12    $ 6,420.44    $ 5,579.93    $ 840.51    $ 836,148.61    $ 2,625.00    $ 9,045.44
    56    $ 836,148.61    $ 6,420.44    $ 5,574.32    $ 846.12    $ 835,302.50    $ 2,625.00    $ 9,045.44
    57    $ 835,302.50    $ 6,420.44    $ 5,568.68    $ 851.76    $ 834,450.74    $ 2,625.00    $ 9,045.44
    58    $ 834,450.74    $ 6,420.44    $ 5,563.00    $ 857.44    $ 833,593.30    $ 2,625.00    $ 9,045.44
    59    $ 833,593.30    $ 6,420.44    $ 5,557.29    $ 863.15    $ 832,730.15    $ 2,625.00    $ 9,045.44
    60    $ 832,730.15    $ 6,420.44    $ 5,551.53    $ 868.91    $ 831,861.25    $ 2,625.00    $ 9,045.44
    61    $ 831,861.25    $ 6,420.44    $ 5,545.74    $ 874.70    $ 830,986.55    $ 2,625.00    $ 9,045.44
    62    $ 830,986.55    $ 6,420.44    $ 5,539.91    $ 880.53    $ 830,106.02    $ 2,625.00    $ 9,045.44
    63    $ 830,106.02    $ 6,420.44    $ 5,534.04    $ 886.40    $ 829,219.62    $ 2,625.00    $ 9,045.44
    64    $ 829,219.62    $ 6,420.44    $ 5,528.13    $ 892.31    $ 828,327.31    $ 2,625.00    $ 9,045.44
    65    $ 828,327.31    $ 6,420.44    $ 5,522.18    $ 898.26    $ 827,429.05    $ 2,625.00    $ 9,045.44
    66    $ 827,429.05    $ 6,420.44    $ 5,516.19    $ 904.25    $ 826,524.80    $ 2,625.00    $ 9,045.44
    67    $ 826,524.80    $ 6,420.44    $ 5,510.17    $ 910.27    $ 825,614.53    $ 2,625.00    $ 9,045.44
    68    $ 825,614.53    $ 6,420.44    $ 5,504.10    $ 916.34    $ 824,698.19    $ 2,625.00    $ 9,045.44
    69    $ 824,698.19    $ 6,420.44    $ 5,497.99    $ 922.45    $ 823,775.73    $ 2,625.00    $ 9,045.44
    70    $ 823,775.73    $ 6,420.44    $ 5,491.84    $ 928.60    $ 822,847.13    $ 2,625.00    $ 9,045.44
    71    $ 822,847.13    $ 6,420.44    $ 5,485.65    $ 934.79    $ 821,912.34    $ 2,625.00    $ 9,045.44
    72    $ 821,912.34    $ 6,420.44    $ 5,479.42    $ 941.02    $ 820,971.32    $ 2,625.00    $ 9,045.44
    73    $ 820,971.32    $ 6,420.44    $ 5,473.14    $ 947.30    $ 820,024.02    $ 2,625.00    $ 9,045.44
    74    $ 820,024.02    $ 6,420.44    $ 5,466.83    $ 953.61    $ 819,070.41    $ 2,625.00    $ 9,045.44
    75    $ 819,070.41    $ 6,420.44    $ 5,460.47    $ 959.97    $ 818,110.43    $ 2,625.00    $ 9,045.44
    76    $ 818,110.43    $ 6,420.44    $ 5,454.07    $ 966.37    $ 817,144.06    $ 2,625.00    $ 9,045.44
    77    $ 817,144.06    $ 6,420.44    $ 5,447.63    $ 972.81    $ 816,171.25    $ 2,625.00    $ 9,045.44
    78    $ 816,171.25    $ 6,420.44    $ 5,441.14    $ 979.30    $ 815,191.95    $ 2,625.00    $ 9,045.44
    79    $ 815,191.95    $ 6,420.44    $ 5,434.61    $ 985.83    $ 814,206.13    $ 2,625.00    $ 9,045.44
    80    $ 814,206.13    $ 6,420.44    $ 5,428.04    $ 992.40    $ 813,213.73    $ 2,625.00    $ 9,045.44
    81    $ 813,213.73    $ 6,420.44    $ 5,421.42    $ 999.02    $ 812,214.71    $ 2,625.00    $ 9,045.44
    82    $ 812,214.71    $ 6,420.44    $ 5,414.76    $ 1,005.68    $ 811,209.04    $ 2,625.00    $ 9,045.44
    83    $ 811,209.04    $ 6,420.44    $ 5,408.06    $ 1,012.38    $ 810,196.66    $ 2,625.00    $ 9,045.44
    84    $ 810,196.66    $ 6,420.44    $ 5,401.31    $ 1,019.13    $ 809,177.53    $ 2,625.00    $ 9,045.44
    85    $ 809,177.53    $ 6,420.44    $ 5,394.52    $ 1,025.92    $ 808,151.60    $ 2,625.00    $ 9,045.44
    86    $ 808,151.60    $ 6,420.44    $ 5,387.68    $ 1,032.76    $ 807,118.84    $ 2,625.00    $ 9,045.44
    87    $ 807,118.84    $ 6,420.44    $ 5,380.79    $ 1,039.65    $ 806,079.19    $ 2,625.00    $ 9,045.44
    88    $ 806,079.19    $ 6,420.44    $ 5,373.86    $ 1,046.58    $ 805,032.61    $ 2,625.00    $ 9,045.44
    89    $ 805,032.61    $ 6,420.44    $ 5,366.88    $ 1,053.56    $ 803,979.06    $ 2,625.00    $ 9,045.44
    90    $ 803,979.06    $ 6,420.44    $ 5,359.86    $ 1,060.58    $ 802,918.48    $ 2,625.00    $ 9,045.44
    91    $ 802,918.48    $ 6,420.44    $ 5,352.79    $ 1,067.65    $ 801,850.83    $ 2,625.00    $ 9,045.44
    92    $ 801,850.83    $ 6,420.44    $ 5,345.67    $ 1,074.77    $ 800,776.06    $ 2,625.00    $ 9,045.44
    93    $ 800,776.06    $ 6,420.44    $ 5,338.51    $ 1,081.93    $ 799,694.13    $ 2,625.00    $ 9,045.44
    94    $ 799,694.13    $ 6,420.44    $ 5,331.29    $ 1,089.15    $ 798,604.98    $ 2,625.00    $ 9,045.44
    95    $ 798,604.98    $ 6,420.44    $ 5,324.03    $ 1,096.41    $ 797,508.58    $ 2,625.00    $ 9,045.44
    96    $ 797,508.58    $ 6,420.44    $ 5,316.72    $ 1,103.72    $ 796,404.86    $ 2,625.00    $ 9,045.44
    97    $ 796,404.86    $ 6,420.44    $ 5,309.37    $ 1,111.07    $ 795,293.79    $ 2,625.00    $ 9,045.44
    98    $ 795,293.79    $ 6,420.44    $ 5,301.96    $ 1,118.48    $ 794,175.30    $ 2,625.00    $ 9,045.44
    99    $ 794,175.30    $ 6,420.44    $ 5,294.50    $ 1,125.94    $ 793,049.37    $ 2,625.00    $ 9,045.44
    100    $ 793,049.37    $ 6,420.44    $ 5,287.00    $ 1,133.44    $ 791,915.92    $ 2,625.00    $ 9,045.44
    101    $ 791,915.92    $ 6,420.44    $ 5,279.44    $ 1,141.00    $ 790,774.92    $ 2,625.00    $ 9,045.44
    102    $ 790,774.92    $ 6,420.44    $ 5,271.83    $ 1,148.61    $ 789,626.31    $ 2,625.00    $ 9,045.44
    103    $ 789,626.31    $ 6,420.44    $ 5,264.18    $ 1,156.26    $ 788,470.05    $ 2,625.00    $ 9,045.44
    104    $ 788,470.05    $ 6,420.44    $ 5,256.47    $ 1,163.97    $ 787,306.08    $ 2,625.00    $ 9,045.44
    105    $ 787,306.08    $ ...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?