Can someone help me list them with Excel, please. 1.Determine the Monthly Mortga
ID: 2811541 • Letter: C
Question
Can someone help me list them with Excel, please.
1.Determine the Monthly Mortgage Payment for a $1,000,000 loan at 3.00% and a 5-year amortization schedule.
2.How much Principal remains after 2 years?
3.What is the Interest and Principal portion of the 8th Monthly Payment?
4.If Year 1’s NOI is $400,000, what is the DSCR?
5.Lenders are willing to finance small, multifamily properties at a 70% LTV, and are extending loans at 4.00% on a 30-year amortization. Equity investors are seeking a yield of around 11.00%. Derive the cap rate using the Band of Investment/Ellwood Method.
Explanation / Answer
1.
If the loan amount is P, rate on interest (monthly is r, and loan term is n the EMI will be
EMI = P*r[(1 +r)^n]/ [(1+ r)^n- 1]
Where,
Loan amount (P) = $1000000
Time (n) = 60 Period
Interest rate [r] = 0.25% /Period
Let's put all the values in the formula to calculate EMI
EMI = 1000000*0.0025[(1 +0.0025)^60]/ [(1+ 0.0025)^60- 1]
= 2500[(1.0025)^60]/ [(1.0025)^60- 1]
= 2500[1.1616167816]/ [1.1616167816- 1]
= 2500[1.1616167816]/ [0.1616167816]
= 2500[7.18747626391293]
= 17968.69
So EMI will be $17968.69
2.
If the total loan amount is P, periodic interest rate is (i), and total loan period is (t), and number of emi paid is [c]
we can find remaining loan amount (B) using below formula
B = P [(1 + i) ^n - (1 + i) ^c]/ [(1 + i) ^n -1]
Where,
Loan amount (P) = $1000000
Interest rate = 0.25% per period
Total loan term (n) = 60 months
Loan paid for period [c] = 24 months
Let's put all the values in the formula
B = 1000000* [(1 + 0.0025) ^60 - (1 + 0.0025) ^24]/ [(1 + 0.0025) ^60 - 1]
= 1000000* [(1.0025) ^60 - (1.0025) ^24]/ [(1.0025) ^60 - 1]
= 1000000* [1.1616167816 - 1.0617570443]/ [1.1616167816 - 1]
= 1000000* [0.0998597373/ 0.1616167816]
= 1000000* 0.6178797543
= 617879.75
So remaining loan amount is $617879.75
3.
Period
Loan balance
Periodic Payment (EMI)
Interest
Principle Payment
Remaining Loan
0
1000000
1
1000000.00
17968.69
2500.00
15468.69
984531.31
2
984531.31
17968.69
2461.33
15507.36
969023.95
3
969023.95
17968.69
2422.56
15546.13
953477.82
4
953477.82
17968.69
2383.69
15585.00
937892.82
5
937892.82
17968.69
2344.73
15623.96
922268.86
6
922268.86
17968.69
2305.67
15663.02
906605.84
7
906605.84
17968.69
2266.51
15702.18
890903.67
8
890903.67
17968.69
2227.26
15741.43
875162.24
In 8th payment Interest = $2227.26 and Principle = 15741.43
Period
Loan balance
Periodic Payment (EMI)
Interest
Principle Payment
Remaining Loan
0
1000000
1
1000000.00
17968.69
2500.00
15468.69
984531.31
2
984531.31
17968.69
2461.33
15507.36
969023.95
3
969023.95
17968.69
2422.56
15546.13
953477.82
4
953477.82
17968.69
2383.69
15585.00
937892.82
5
937892.82
17968.69
2344.73
15623.96
922268.86
6
922268.86
17968.69
2305.67
15663.02
906605.84
7
906605.84
17968.69
2266.51
15702.18
890903.67
8
890903.67
17968.69
2227.26
15741.43
875162.24
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.