2. You just took a $315,000, 30-year loan. Payments at the end of each month are...

2. You just took a $315,000, 30-year loan. Payments at the end of each month are flat (equal in every month) at an annual interest rate of 3.75 percent.

a)Calculate the monthly payment.

b)Provide the appropriate loan table, showing the breakdown in each month between principal repayment and interest.

(PLEASE SHOW STEP-BY-STEP EXCEL FORMULA WITH FUNCTIONS)

Expert Solution

Annual interest rate =	3.75%
Years	30
No. of payments in a year	12
Total no. of periods30*12=	360
Amount	315000

Formula for monthly payment = PMT(rate per period, no. of periods, amount of loan)
PMT(0.0375/12,360,315000)

Formula for principal payment= PPMT(rate per period, period number, no. of periods, amount of loan)
PPMT(0.0375/12,F22,360,315000)

Formula for interest payment = IPMT(rate per period, period number, no. of periods, amount of loan)
IPMT(0.0375/12,F22,360,315000)

Balance formula = Amount of loan -Principal payment



Payment number	Cell refernce no.	Payment	Principal	Interest	Balance
1	F22	-$1,458.81	-$474.44	-$984.38	$314,525.56
2	F23	-$1,458.81	-$475.92	-$982.89	$314,049.64
3	F24	-$1,458.81	-$477.41	-$981.41	$313,572.23
4	F25	-$1,458.81	-$478.90	-$979.91	$313,093.33
5	F26	-$1,458.81	-$480.40	-$978.42	$312,612.93
6	F27	-$1,458.81	-$481.90	-$976.92	$312,131.03
7	F28	-$1,458.81	-$483.40	-$975.41	$311,647.63
8	F29	-$1,458.81	-$484.92	-$973.90	$311,162.71
9	F30	-$1,458.81	-$486.43	-$972.38	$310,676.28
10	F31	-$1,458.81	-$487.95	-$970.86	$310,188.33
11	F32	-$1,458.81	-$489.48	-$969.34	$309,698.86
12	F33	-$1,458.81	-$491.01	-$967.81	$309,207.85

So, monthly payment is $1,458.81.

and loan table is abovementioned.

jeff jeffy answered 1 year ago

A 30-year loan of 1,000 is repaid with payments at the end of each year. Each...

A 30-year loan of 1,000 is repaid with payments at the end of each year. Each of the first ten payments equals the amount of interest due. Each of the next ten payments equals 150% of the amount of interst due. Each of the last ten payments is X. The lender charges interest at an annual effective rate of 10%. Calculate X.

a 30 year loan of 1000 is repaid with payments at the end of each year....

a 30 year loan of 1000 is repaid with payments at the end of each year. Each of the first 10 payments equals the amount of interest due. Each of the next 10 payments equals 150% of the amount of interest due. Each of the last 10 payments is X. The lender charges interest at an effective annual rate of 10%. Calculate X. please explain, having much trouble with this problem, Thanks!

A 18 year loan is being repaid with level payments at the end of each month....

A 18 year loan is being repaid with level payments at the end of each month. The loan rate of interest is 15.6% compounded monthly. In which month is the interest portion approximately equal to 5 times principal the portion? Give an integer answer.

A 30-year annuity has end-of-month payments. The first year the payments are $120 each. In subsequent...

A 30-year annuity has end-of-month payments. The first year the payments are $120 each. In subsequent years the monthly payment increases by $5 over what it was the previous year. Find the accumulated value of this annuity if AEIR=3% A. 84,820 B. 42,390 C. 105,070 D. 100,620 E. 41,560

Amortize a 30-year, $120,000 loan with end-of-month payments. The APR is 12%. What is the monthly...

Amortize a 30-year, $120,000 loan with end-of-month payments. The APR is 12%. What is the monthly payment? What are the interest and repayment portions of the payment in month 12? What is the ending balance after one year (month 12)?

A 20 year loan with payments at the end of each year involves payments of $1,...

A 20 year loan with payments at the end of each year involves payments of $1, 000 for the first 10 years, and then for the next 10 years payments are 3% larger than the previous year’s payment. If effective annual interest is 5% then find the original loan amount. please dont solve using excel

A loan of $30,000 is paid off in 36 payments at the end of each month...

A loan of $30,000 is paid off in 36 payments at the end of each month in the following way: Payments of $750 are made at the end of the month for the first 12 months. Payments of $750 + x are made at the end of the month for the second 12 months. Payments of $750 + 2x are made at the end of the month for the last 12 months. What should x be if the nominal monthly...

A loan of $330,000 is amortized over 30 years with payments at the end of each...

A loan of $330,000 is amortized over 30 years with payments at the end of each month and an interest rate of 6.9%, compounded monthly. Use Excel to create an amortization table showing, for each of the 360 payments, the beginning balance, the interest owed, the principal, the payment amount, and the ending balance. Answer the following, rounding to the nearest penny. a) Find the amount of each payment. $ b) Find the total amount of interest paid during the...

A loan of $490,000 is amortized over 30 years with payments at the end of each...

A loan of $490,000 is amortized over 30 years with payments at the end of each month and an interest rate of 8.1%, compounded monthly. Use Excel to create an amortization table showing, for each of the 360 payments, the beginning balance, the interest owed, the principal, the payment amount, and the ending balance. Answer the following, rounding to the nearest penny. a) Find the amount of each payment. $ b) Find the total amount of interest paid during the...

A 10-year loan of 120,000 is to be repaid with payments at the end of each...

A 10-year loan of 120,000 is to be repaid with payments at the end of each month. Interest is at an annual effective rate of 6.00%. The first monthly payment is 800. Each additional payment will be k more than the previous month payment. Find k.

Question