Create specialized Excel mortgage formula for home sellers holding a mortgage

[hunter0987]

Hello. This is my first experience posting to a forum, so please forgive any mistakes. I am a homeowner intending to hold my first mortgage on a home sale with variables beyond standard calculations I could find. I used Excel years ago before retiring, so I’m hopeful to utilize Excel inputing data as time passes. Parameters: $100,000 for 30 year mortgage at 8% compounded quarterly, with entries for late fees and negative amortization for skipped payments, with sporadic lump sum payments applied towards current mortgage status. My quandary is how to determine where interest and principle payments must be applied in extenuating circumstances that deviate from the norm. I am selling to a young but experienced construction business owner that makes profits sporadically through the year unable to qualify for a conventional mortgage. I don’t know if this is an appropriate question to ask, but it is my situation that I hope could also help others holding their first home mortgage as part of their retirement income. I thank everyone in advance for your time reading and perhaps contributing to helping me.

 

[Dave Patton]

Welcome to the forum. N.B. I do not know the rules, regulations, etc. in your county and I do not know the specifics of your agreement.
Are the payments Quarterly? Is the interest added quarterly? What do you mean by negative amortization?
How are late fees calculated?
You can copy my post to a clean sheet. Click on the icon below the f(x) in the heading and move to your sheet and paste into cell A1.
Please advise what changes you require.
My post shows just the first 20 rows; you can copy the information down for the full 360 months.

T202401a.xlsm
	A	B	C	D	E	F	G	H
1	Amount	100,000.00						Mortgage
2	Term Years	30						$2,204.81
3	Rate	8%
4	Payments	Quarterly	?
5	Late fees		?
6								Balance
7	#	Payment Mo	Date	Payment	Interest	Extra Payment	Late fees	100,000.00
8	1		31-01-24					100,000.00
9	2		29-02-24					100,000.00
10	3	TRUE	31-03-24	2,204.81	2,000.00			99,795.19
11	4		30-04-24					99,795.19
12	5		31-05-24					99,795.19
13	6	TRUE	30-06-24	2,204.81	1,995.90			99,586.28
14	7		31-07-24					99,586.28
15	8		31-08-24					99,586.28
16	9	TRUE	30-09-24	2,204.81	1,991.73			99,373.20
17	10		31-10-24					99,373.20
18	11		30-11-24					99,373.20
19	12	TRUE	31-12-24	2,204.81	1,987.46			99,155.85
20	13		31-01-25					99,155.85
5a

Cell Formulas
Range	Formula
H2	H2	=PMT(B3/4,30*4,-B1)
B8:B20	B8	=IF(MOD(A8,3)=0,TRUE,"")
D8:D20	D8	=IF(B8=TRUE,$H$2,0)
E8:E20	E8	=IF(B8=TRUE,$B$3/4*H7,0)
C9:C20	C9	=EOMONTH(C8,1)
H7	H7	=B1
H8:H20	H8	=H7-D8+E8-F8+G8

 

[hunter0987]

Hi Dave. Thank you so much for your reply. I will study what you sent me and try to digest and understand it before trying anything further, since it’s been 10 to 15 years since I worked with Excel and need to ramp up my competence. This looks like the right place to begin. Yes, the interest is compounded quarterly rather than annuall. The late fee is 10% of the payment amount. The payments are monthly and when a payment goes unpaid by the next due date, the interest from the missed payment gets added to the loan principle. This is a final method to prevent foreclosure if intrest alone can not be paid in full. This is a high risk mortgage, so this structure protects my interests, while extending the opportunity for this buyer to have his chance to achieve becoming current as time progresses, at which point some of these restrictions can become relaxed. Thank you again for extending this helping hand. I will try to reply back in a week or so, once I can grasp what you were kind enough to lay out for me.

 

[Dave Patton]

I edited the suggestion to show monthly payments. This example shows the interest compounding consistent with the payment schedule (monthly)
and not Quarterly as you mentioned.

This example may be a better starting point for your review.
You could consider a clause that advanced payments are "banked"/considered before legal action to foreclose.

T202401a.xlsm
	A	B	C	D	E	F	G	H
1	Amount	100,000.00						Mortgage
2	Term Years	30						$733.76
3	Rate	8%
4	Payments	Monthly
5	Late fees	10%
6								Balance
7	#		Date	Payment	Interest	Extra Payment	Late fees	100,000.00
8	1		31-01-24	733.76	666.67			99,932.90
9	2		29-02-24	733.76	666.22	2,000.00		97,865.36
10	3		31-03-24	733.76	652.44			97,784.03
11	4		30-04-24	733.76	651.89			97,702.16
12	5		31-05-24		651.35			98,353.50
13	6		30-06-24	1,467.52	655.69		6.03	97,547.70
14	7		31-07-24	733.76	650.32			97,464.25
15	8		31-08-24	733.76	649.76			97,380.25
16	9		30-09-24	733.76	649.20			97,295.69
17	10		31-10-24	733.76	648.64			97,210.56
18	11		30-11-24	733.76	648.07			97,124.87
19	12		31-12-24	733.76	647.50			97,038.60
20	13		31-01-25	733.76	646.92			96,951.76
5aa

Cell Formulas
Range	Formula
H2	H2	=PMT(B3/12,30*12,-B1)
D8:D11,D14:D20	D8	=$H$2
E8:E20	E8	=$B$3/12*H7
D13	D13	=$H$2+733.76
G13	G13	=H2*B5/365*(C13-C12)
H7	H7	=B1
H8:H20	H8	=H7-D8+E8-F8+G8