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

Free

70.2K

Verified Solution

Question

Finance

2. You just took a $315,000, 30-year loan. Payments at the endof each month are flat (equal in every month) at an annual interestrate of 3.75 percent.
a)Calculate the monthly payment.
b)Provide the appropriate loan table, showing the breakdown ineach month between principal repayment and interest.
(PLEASE SHOW STEP-BY-STEP EXCEL FORMULA WITH FUNCTIONS)

Answer & Explanation Solved by verified expert

4.3 Ratings (716 Votes)

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.

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

2. You just took a $315,000, 30-year loan. Payments at the endof each month are flat (equal in every month) at an annual interestrate of 3.75 percent.a)Calculate the monthly payment.b)Provide the appropriate loan table, showing the breakdown ineach month between principal repayment and interest.(PLEASE SHOW STEP-BY-STEP EXCEL FORMULA WITH FUNCTIONS)