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

Question

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 imaginationExtras: 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

Neenisha · Accepted Answer

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.

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...

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

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	1	2	3