Determine monthly repayment for 25-yr mortgage, including interest-free years

[gravanoc]

I have a mortgage calculator sheet that originally just determines the monthly repayment & a few other metrics, but now I need to expand it to include an interest-free period. I am unsure if my calculations for the 2nd part are headed in the right direction, and the monthly repayment for the interest-free period just seems wrong.

Original look (cell references in [ ]'s)

[A1] APR	[B1] 4.9%
[A2] Monthly Flat	[B2] =(B1+1)^(1/12)-1 (result: 0.40%)
[A3] Loan Period (mos)	[B3] 300
[A4] Cume Discount Factor	[B4] =1/B2-1/(B2*(B2+1)^B3) (result: $174.64)
[A5] Loan Amount	[B5] $1,000,000
[A6] Monthly Repayment	[B6] =B5/B4 (result: $5726)

My current model:

[A1] APR	[B1] 4.9%
[A2] Monthly Flat (principal & interest)	[B2] =(B1+1)^(1/12)-1 (result: 0.40%)
[A3] Loan Period (mos)	[B3] 300
[A4] Interest-free Period (mos)	[B4] 48
[A5] Cume Discount Factor	[B5] =1/B2-1/(B2*(B2+1)^(B3-B4)) (result: $158.67)
[A6] Cume Discount Factor (dur. IF period)	[B6] =1/B2-1/(B2*(B2+1)^(B4)) (result: $43.60)
[A7] Loan Amount	[B7] $1,000,000
[A8] Monthly Repayment (interest + principal)	[B8] =B7/B5 (result: $6659)
[A9] Monthly Repayment (principal only)	[B9] =B7/B6 (result: $24,233) wrong
[A10] Total Repayment	[B10] ?

 

[gravanoc]

Ok, so the full scope of this is that I have a 25-yr (300 mos) mortgage for $1 mil with a 4.9% interest rate. The formula used to determine the Monthly Flat Rate is above in B2 of either table, resulting in 0.40%. The cumulative discount factor is then determined using a formula in B4 of the first table and B5 or B6 from the second table. I believe those calculations are correct, but my issue is then determining what the monthly repayment will be during the 4-yr period where only the principal $1 mil is paid on, and what the monthly repayment will be during the other 21-yrs with the 4.9% interest rate. Is there a Financial formula or something that can be used here that makes sense?

 

[ebea]

How would you calculate the free interest period, when you have no interest set? You only have the 4.9% APR. Without knowing the difference between these 2 factors (principal & interest), you can't calculate the free interest periods amount.

 

[ebea]

I did not read your #2 reply fully maybe this here, is what your looking for. Formula's are for your current model.

Principal payment for 1 Month: =PPMT(B1/12;1;B3;-B7)
Interest payment for 1 Month: =IPMT(B1/12;1;B3;-B7)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: determine_monthly_repayment_for_25yr_mortgage
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ebea]

mortgage-repay.xlsx
	A	B	C	D	E	F
1	Monthly interest	4,90%		Principal	Interest	Total
2	Yearly interest	0,00%
3
4	Loan period	300	300	1.000.000,00	736.335,83	1.736.335,83
5	Free period	48	48	81.813,73	2,04	918.186,27
6	Rest month period	252	252	918.186,27	553.746,73	1.471.933,00
7
8	1 Month period	1	1	1.704,45	4.083,33	5.787,79
9
10
11	Loan amount	1.000.000
Ark1

Cell Formulas
Range	Formula
D4	D4	=CUMPRINC(B1/12,B4,B11,1,B4,0)*-1
E4	E4	=CUMIPMT(B1/12,B4,B11,1,B4,0)*-1
F4	F4	=PMT(B1/12,B4/12*12,-B11)*B4
D5	D5	=D8*B5
E5	E5	=CUMIPMT(B2/12,B5,B11,1,B5,0)*-1
F5	F5	=B11-D5
D6	D6	=CUMPRINC(B1/12,B6,F5,1,B6,0)*-1
E6	E6	=CUMIPMT(B1/12,B6,F5,1,B6,0)*-1
F6	F6	=D6+E6
D8	D8	=PPMT(B1/12,B8,B4,-B11)
E8	E8	=IPMT(B1/12,B8,B4,-B11)
F8	F8	=PMT(B1/12,B4/12*12,-B11)

 

[Dave Patton]

What is your known information and what are you trying to achieve?
Can you provide more information on methods, examples, and other details.
Are the payments the same for all 300 months?
Did you try using an amortization schedule with your known information?
N.B.
The mortgage will be amortized in 25 years with interest at 4.9% and payments of 5787.79 or
it will be amortized with payments of 5787.79 and no interest for 4 years and then interest at 7.697%.

Mortgage APR.xlsm
	A	B	C	D
1	Mortgage amount	1,000,000.00
2	Rate	4.90%	7.697%	7.697%
3	Term	25 Years
4	Term no interest	4 Years
5	Payment	5,787.79
6				Balance
7	Payment #	Payment	Interest	1,000,000.00
8	1	5,787.79	0.00	994,212.21
9	2	5,787.79	0.00	988,424.42
3aa

Cell Formulas
Range	Formula
C2	C2	=RATE(B3*12-B4*12,B5,-B1+B4*12*B5)*12
D2	D2	=RATE(252,B5,-722186.08)*12
B5	B5	=ROUND(PMT(B2/12,B3*12,-B1),2)
D7	D7	=B1
B8:B9	B8	=$B$5
C8:C9	C8	=ROUND(IF(A8<=48,0,$C$2/12)*D7,2)
D8:D9	D8	=D7-B8+C8