MORTGAGE ACCOUNTING
Copyright 2003 Mortgage-investments.com, Inc.							http://www.mortgage-investments.com
Please read below before using.
Make one copy of this spreadsheet for each mortgage you own.  Save using File> Save As. For example Save As mortgage-accounting-john-doe.xls
As you can see, the spreadsheet will fill up with the expected payments and due dates. Write over them as you receive the actual payments.
Only enter in the yellow boxes, everything else is calculated. You can write over the late charge if you wish, although we do not recommend it.
If the payment is less than required, the difference is added onto the principal outstanding. Also the difference is added onto the next months payment.
If no payment is received, enter 0 in the payment received and the last day of the month as the received date. This adds on the interest, increases the next payment & adds the late charge.
Enter in the "Apply to Escrow" box the amount you want to apply to the escrow account. This will vary as years go by. There should be enough collected to pay the property tax & insurance as they fall due, but not more than 2 months more than that.
Interest is added on a monthly not daily basis.
Disclaimer. This spreadsheet is believed to be accurate for USA 365 day year mortgages but we do not guarantee it's accuracy and accept no responsibilty for any errors.
If in doubt, ask your CPA to check your numbers.
Many of the calculations in this spreadsheet are locked for security purposes. Please don't ask us for the unlock code.
If we gave it out it would be easy for someone to add a virus in a macro and we would get the blame.
You are welcome to share this spreadsheet with friends and clients provided it is not altered in any way. To insure they get the latest version, we suggest they download it from our web site.
Borrower:			Address:							City:		State and Zip
Social Security No.			Work phone:							Home Phone:
Co_Borrower			Address:							City:		State and Zip
Social Security No.			Work phone:							Home Phone:
	Mortgage Summary													ENTER THESE NUMBERS BELOW
	Annual Loan Payments						$3,171.60							Loan Principal Amount		$20,000.00
	Monthly Payments						$264.30							Annual Interest Rate		10.00%
	Interest in First Calendar Year						$0.00							Loan Period in Years		10		Enter 999 for interest only balloon
	Interest Over Term of Loan						$11,716.00							Year first payment due		2004
	Sum of All Payments						$31,716.00							Month first payment due		January
														Due Day		1
														Late Charge		30.62
Payments Received for first 120 months														Days Before Late		12
														Escrow payment (if any)		50.00
Paid	Paid	Paid	Due	Due	Due	Days	Beginning	Payment	Payment	Apply to	Late			Cumulative	Cumulative	Ending		Total in	Date of Escrow	Type of	Amount of
Year	Month	Day	Year	Month	Day	Late	Balance	Due	Received	Escrow	Charge	Principal	Interest	Principal	Interest	Balance		Escrow	Disbursement	Disbursement	Disbursement
	(number)							Inc. Escrow	Inc. Escrow												always neg.
2004	1	1	2004	Jan	1	0	20,000.00	264.30	264.30	0.00	0.00	97.63	166.67	97.63	166.67	19,902.37		0.00							#	##	#
2004	2	1	2004	Feb	1	0	19,902.37	264.30	264.30		0.00	98.45	165.85	196.08	332.52	19,803.92		0.00							#	##	#
2004	3	1	2004	Mar	1	0	19,803.92	264.30	264.30		0.00	99.27	165.03	295.35	497.55	19,704.65		0.00							#	##	#
2004	4	1	2004	Apr	1	0	19,704.65	264.30	264.30		0.00	100.09	164.21	395.44	661.76	19,604.56		0.00							#	##	#
2004	5	1	2004	May	1	0	19,604.56	264.30	264.30		0.00	100.93	163.37	496.37	825.13	19,503.63		0.00							#	##	#
2004	6	1	2004	Jun	1	0	19,503.63	264.30	264.30		0.00	101.77	162.53	598.14	987.66	19,401.86		0.00							#	##	#
2004	7	1	2004	Jul	1	0	19,401.86	264.30	264.30		0.00	102.62	161.68	700.76	1,149.34	19,299.24		0.00							#	##	#
2004	8	1	2004	Aug	1	0	19,299.24	264.30	264.30		0.00	103.47	160.83	804.23	1,310.17	19,195.77		0.00							#	##	#
2004	9	1	2004	Sep	1	0	19,195.77	264.30	264.30		0.00	104.34	159.96	908.57	1,470.13	19,091.43		0.00							#	##	#
2004	10	1	2004	Oct	1	0	19,091.43	264.30	264.30		0.00	105.20	159.10	1,013.77	1,629.23	18,986.23		0.00							#	##	#
2004	11	1	2004	Nov	1	0	18,986.23	264.30	264.30		0.00	106.08	158.22	1,119.85	1,787.45	18,880.15		0.00							#	##	#
2004	12	1	2004	Dec	1	0	18,880.15	264.30	264.30		0.00	106.97	157.33	1,226.82	1,944.78	18,773.18		0.00							#	##	#
2005	1	1	2005	Jan	1	0	18,773.18	264.30	264.30		0.00	107.86	156.44	1,334.68	2,101.22	18,665.32		0.00							#	##	#
2005	2	1	2005	Feb	1	0	18,665.32	264.30	264.30		0.00	108.76	155.54	1,443.44	2,256.76	18,556.56		0.00							#	##	#
2005	3	1	2005	Mar	1	0	18,556.56	264.30	264.30		0.00	109.66	154.64	1,553.10	2,411.40	18,446.90		0.00							#	##	#
2005	4	1	2005	Apr	1	0	18,446.90	264.30	264.30		0.00	110.58	153.72	1,663.68	2,565.12	18,336.32		0.00							#	##	#
2005	5	1	2005	May	1	0	18,336.32	264.30	264.30		0.00	111.50	152.80	1,775.18	2,717.92	18,224.82		0.00							#	##	#
2005	6	1	2005	Jun	1	0	18,224.82	264.30	264.30		0.00	112.43	151.87	1,887.61	2,869.79	18,112.39		0.00							#	##	#
2005	7	1	2005	Jul	1	0	18,112.39	264.30	264.30		0.00	113.36	150.94	2,000.97	3,020.73	17,999.03		0.00							#	##	#
2005	8	1	2005	Aug	1	0	17,999.03	264.30	264.30		0.00	114.31	149.99	2,115.28	3,170.72	17,884.72		0.00							#	##	#
2005	9	1	2005	Sep	1	0	17,884.72	264.30	264.30		0.00	115.26	149.04	2,230.54	3,319.76	17,769.46		0.00							#	##	#
2005	10	1	2005	Oct	1	0	17,769.46	264.30	264.30		0.00	116.22	148.08	2,346.76	3,467.84	17,653.24		0.00							#	##	#
2005	11	1	2005	Nov	1	0	17,653.24	264.30	264.30		0.00	117.19	147.11	2,463.95	3,614.95	17,536.05		0.00							#	##	#
2005	12	1	2005	Dec	1	0	17,536.05