Question

In: Accounting

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


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