Home Loan Amortization; Goal Seeking, Creating Macros, and Creating Summary Information for Management. Goals of this...

Home Loan Amortization; Goal Seeking, Creating Macros, and Creating Summary Information for Management.

Goals of this Project:

1. Freeze Panes to consistently display titles of Loan Amortization Table 2. Learn how to perform Goal Seeking. You will put in three different monthly payment amounts and see the effect on the number of periods. For example, for an 8 percent, 2 point loan, 360 periods, do not pay the points up front, and borrow $400,000, answer the following questions: How many years will it take to pay off the loan if you pay an additional $750 on the Principal? How many years will it take to pay off the loan if you pay an additional $1500 on the Principal? How many years will it take to pay off the loan if you double the monthly payment?

Solutions

Expert Solution

In case of no down payment :

Loan Amount : $400,000

Interest : 8%

Period : 360 months or 30 yrs

Hence the payment of EMI = Interest + Principal

EMI = In excel use function pmt(rate,no. of yrs,loan amt *-1) or P = (Pv*R) / [1 - (1 + R)^(-n)] = $2935

Interest = ($400,000 * 8%) / 12 = $2667

Principal = EMI - Interest = $2667 - $2935 = $268

How many years will it take to pay off the loan if you pay an additional $750 on the Principal?

If $750 is added to EMI then it will take 17yrs 2 months to pay off the the loan.

How many years will it take to pay off the loan if you pay an additional $1500 on the Principal?

If $1500 is added to EMI then it will take 11 yrs 5 months to pay off the the loan.

How many years will it take to pay off the loan if you pay double the monthly payment

If $2935 is added to EMI then it will take 7 yrs 6 months to pay off the the loan.

YEAR	DATE	PAYMENT	INTEREST	PRINCIPAL	ENDING BALANCE
1	2019	ORIGINAL $750 $1500 $2935	$31,602 $31,542 $31,204 $30,559	$3,619 $12,679 $22,016 $39,882	$393,040 $387,321 $377,984 $360,118
2	2020	ORIGINAL $750 $1500 $2935	$31,302 $30,489 $29,377 $27,249	$3,919 $13,731 $23,844 $43,192	$389,121 $373,590 $354,140 $316,926
3	2021	ORIGINAL $750 $1500 $2935	$30,976 $29,350 $27,398 $23,664	$4,244 $14,871 $25,823 $46,777	$384,876 $358,719 $328,317 $46,777
4	2022	ORIGINAL $750 $1500 $2935	$30,624 $28,115 $25,255 $19,781	$4,597 $16,105 $27,966 $50,660	$380,279 $342,614 $300,351 $219,489
5	2023	ORIGINAL $750 $1500 $2935	$30,242 $26,779 $22,934 $15,576	$4,978 $17,442 $30,287 $54,864	$375,301 $325,172 $270,064 $164,625
6	2024	ORIGINAL $750 $1500 $2935	$29,829 $25,331 $20,420 $11,023	$5,391 $18,890 $32,801 $59,418	$369,910 $306,282 $237,263 $105,207
7	2025	ORIGINAL $750 $1500 $2935	$29,382 $23,763 $17,697 $6,091	$5,839 $20,457 $35,523 $64,350	$364,071 $285,825 $201,740 $40,857
8	2026	ORIGINAL $750 $1500 $2935	$28,897 $22,065 $14,749 $1,120	$6,324 $22,155 $38,472 $40,857	$357,747 $263,669 $163,268 $0
9	2027	ORIGINAL $750 $1500 $2935	$28,372 $20,226 $11,556 -	$6,848 $23,994 $41,665 -	$350,899 $239,675 $121,603 -
10	2028	ORIGINAL $750 $1500	$27,804 $18,235 $8,098	$7,417 $25,986 $45,123	$343,482 $213,689 $76,480
11	2029	ORIGINAL $750 $1500	$27,188 $16,078 $4,352	$8,032 $28,143 $48,868	$335,450 $185,547 $27,611
12	2030	ORIGINAL $750 $1500	$26,522 $13,742 $687	$8,699 $30,478 $27,611	$326,750 $155,068 $0
13	2031	ORIGINAL $750 $1500	$25,800 $11,213	$9,421 $33,008	$317,329 $122,060
14	2032	ORIGINAL $750 $1500	$25,018 $8,473	$10,203 $35,748	$307,126 $86,312
15	2033	ORIGINAL $750 $1500	$24,171 $5,506	$11,050 $38,715	$296,076 $47,598
16	2034	ORIGINAL $750 $1500	$23,254 $2,293	$11,967 $41,928	$284,109 $5,669
17	2035	ORIGINAL $750 $1500	$22,260 $51	$12,960 $5,669	$271,149 $0
18	2036	ORIGINAL $750 $1500	$21,185	$14,036	$257,113
19	2037	ORIGINAL $750 $1500	$20,020	$15,201	$241,912
20	2038	ORIGINAL $750 $1500	$18,758	$16,463	$225,449
21	2039	ORIGINAL $750 $1500	$17,392	$17,829	$207,620
22	2040	ORIGINAL $750 $1500	$15,912	$19,309	$188,311
23	2041	ORIGINAL $750 $1500	$14,309	$20,912	$167,400
24	2042	ORIGINAL $750 $1500	$12,574	$22,647	$144,752
25	2043	ORIGINAL $750 $1500	$10,694	$24,527	$120,226
26	2044	ORIGINAL $750 $1500	$8,658	$26,563	$93,663
27	2045	ORIGINAL $750 $1500	$6,453	$28,767	$64,896
28	2046	ORIGINAL $750 $1500	$4,066	$31,155	$33,741
29	2047	ORIGINAL $750 $1500	$1,480	$33,741	$0
30	2048	ORIGINAL $750 $1500

ekkarill92 answered 1 year ago

Next > < Previous

Related Solutions

The primary goal of financial management is creating value by _______

The primary goal of financial management is creating value by _______ A. making investments that are worth more than they cost B. issuing bonds that receive investment-grade ratings C. making investments that pay off sooner rather than later D. taking as little risk as possible

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment:...

Goal-seek Loan Amortization Schedule Amount of the loan: $500,000 Length of the loan: 30 years Payment: Equal annual payment Interest rate: Annual interest rate is 3.0% in year 1, and increases 0.1% in year 2, etc. In other words, the annual interest rates will be 3.0%, 3.1%, 3.2%,…. M1 Assignment - Goal-seek Loan Amortization Schedule Rubric Successfully created year-by-year table with Year, Beginning balance, PMT, interest, principal, ending balance 3 pts Successfully created a column to reflect floating interest rates...

Prepare the first row of a loan amortization schedule based on the following information; the loan...

Prepare the first row of a loan amortization schedule based on the following information; the loan amount is for $17,246 with an annual interest rate of 6%. the loan will be repaid over 38 years with monthly payments. What is the loan payment? What portion of this payment is Interest? What portion of this payment is principal? What is the loan balance after the first monthly payment?

Home: $180,000 with $40,000 down payment. Financed for 20 years at 6% APR. Create loan amortization...

Home: $180,000 with $40,000 down payment. Financed for 20 years at 6% APR. Create loan amortization schedule in excel for constant payment method. Rework with an extra $40 in principal Create a loan amortization schedule in excel for constant amortization method.

The principal goal of financial reporting is... A Providing management with the information necessary to run...

The principal goal of financial reporting is... A Providing management with the information necessary to run their business B Providing investors with sufficient information to allow them to make capital allocation decissions C Providing the SEC with adequate information to perform oversight of company accounting

Management Information System This assignment is about creating a blog and a research about working for...

Management Information System This assignment is about creating a blog and a research about working for The Office of Career Services at a school. Answer the following questions based on your team project The Office of Career Services. Pretty much we are making up this job so you can include any vendors and any inter-enterprise. (you can just make-up your own idea or use imagination) . What are CRM, ERP and EAI? Who are the major ERP vendors? Does your...

Health Information Management Budget of $2500 to spend towards meeting goal on behavior with colleages

Management accounting measures, analyzes, and reports financial and nonfinancial information to internal managers. The goal is...

Management accounting measures, analyzes, and reports financial and nonfinancial information to internal managers. The goal is to use past performance to predict the future. The internal reports should plainly inform managers of the financial results of actual operations. The reports should also show how activities can be changed to affect and improve what will happen in the future. Business operations are complex sets of activities, and to maximize profit considerable information, analysis, and decision making is required in advance of...

what is the main goal of the AHIMA's (American Health Information Management assocciate) and how does...

what is the main goal of the AHIMA's (American Health Information Management assocciate) and how does Code of Ethics has an impact on HIM professionals?

“Large Box Store” management has just set a company goal for all employees (new and old) to watch a video on the basics of customer service on their own time (in the back office or at home) by the end of the month.

“Large Box Store” management has just set a company goal for all employees (new and old) to watch a video on the basics of customer service on their own time (in the back office or at home) by the end of the month. The average tenure for their employees is actually 2 years and nearly all have already received performance appraisals regarding their customer service abilities. As the deadline is approaching management notices that very few employees have actually watched...

Subjects