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 264.30 264.30   0.00 118.17 146.13 2,582.12 3,761.08 17,417.88   0.00             # ## #
2006 1 1 2006 Jan 1 0 17,417.88 264.30 264.30   0.00 119.15 145.15 2,701.27 3,906.23 17,298.73   0.00             # ## #
2006 2 1 2006 Feb 1 0 17,298.73 264.30 264.30   0.00 120.14 144.16 2,821.41 4,050.39 17,178.59   0.00             # ## #
2006 3 1 2006 Mar 1 0 17,178.59 264.30 264.30   0.00 121.15 143.15 2,942.56 4,193.54 17,057.44   0.00             # ## #
2006 4 1 2006 Apr 1 0 17,057.44 264.30 264.30   0.00 122.15 142.15 3,064.71 4,335.69 16,935.29   0.00             # ## #
2006 5 1 2006 May 1 0 16,935.29 264.30 264.30   0.00 123.17 141.13 3,187.88 4,476.82 16,812.12   0.00             # ## #
2006 6 1 2006 Jun 1 0 16,812.12 264.30 264.30   0.00 124.20 140.10 3,312.08 4,616.92 16,687.92   0.00             # ## #
2006 7 1 2006 Jul 1 0 16,687.92 264.30 264.30   0.00 125.23 139.07 3,437.31 4,755.99 16,562.69   0.00             # ## #
2006 8 1 2006 Aug 1 0 16,562.69 264.30 264.30   0.00 126.28 138.02 3,563.59 4,894.01 16,436.41   0.00             # ## #
2006 9 1 2006 Sep 1 0 16,436.41 264.30 264.30   0.00 127.33 136.97 3,690.92 5,030.98 16,309.08   0.00             # ## #
2006 10 1 2006 Oct 1 0 16,309.08 264.30 264.30   0.00 128.39 135.91 3,819.31 5,166.89 16,180.69   0.00             # ## #
2006 11 1 2006 Nov 1 0 16,180.69 264.30 264.30   0.00 129.46 134.84 3,948.77 5,301.73 16,051.23   0.00             # ## #
2006 12 1 2006 Dec 1 0 16,051.23 264.30 264.30   0.00 130.54 133.76 4,079.31 5,435.49 15,920.69   0.00             # ## #
2007 1 1 2007 Jan 1 0 15,920.69 264.30 264.30   0.00 131.63 132.67 4,210.94 5,568.16 15,789.06   0.00             # ## #
2007 2 1 2007 Feb 1 0 15,789.06 264.30 264.30   0.00 132.72 131.58 4,343.66 5,699.74 15,656.34   0.00             # ## #
2007 3 1 2007 Mar 1 0 15,656.34 264.30 264.30   0.00 133.83 130.47 4,477.49 5,830.21 15,522.51   0.00             # ## #
2007 4 1 2007 Apr 1 0 15,522.51 264.30 264.30   0.00 134.95 129.35 4,612.44 5,959.56 15,387.56   0.00             # ## #
2007 5 1 2007 May 1 0 15,387.56 264.30 264.30   0.00 136.07 128.23 4,748.51 6,087.79 15,251.49   0.00             # ## #
2007 6 1 2007 Jun 1 0 15,251.49 264.30 264.30   0.00 137.20 127.10 4,885.71 6,214.89 15,114.29   0.00             # ## #
2007 7 1 2007 Jul 1 0 15,114.29 264.30 264.30   0.00 138.35 125.95 5,024.06 6,340.84 14,975.94   0.00             # ## #
2007 8 1 2007 Aug 1 0 14,975.94 264.30 264.30   0.00 139.50 124.80 5,163.56 6,465.64 14,836.44   0.00             # ## #
2007 9 1 2007 Sep 1 0 14,836.44 264.30 264.30   0.00 140.66 123.64 5,304.22 6,589.28 14,695.78   0.00             # ## #
2007 10 1 2007 Oct 1 0 14,695.78 264.30 264.30   0.00 141.84 122.46 5,446.06 6,711.74 14,553.94   0.00             # ## #
2007 11 1 2007 Nov 1 0 14,553.94 264.30 264.30   0.00 143.02 121.28 5,589.08 6,833.02 14,410.92   0.00             # ## #
2007 12 1 2007 Dec 1 0 14,410.92 264.30 264.30   0.00 144.21 120.09 5,733.29 6,953.11 14,266.71   0.00             # ## #
2008 1 1 2008 Jan 1 0 14,266.71 264.30 264.30   0.00 145.41 118.89 5,878.70 7,072.00 14,121.30   0.00             # ## #
2008 2 1 2008 Feb 1 0 14,121.30 264.30 264.30   0.00 146.62 117.68 6,025.32 7,189.68 13,974.68   0.00             # ## #
2008 3 1 2008 Mar 1 0 13,974.68 264.30 264.30   0.00 147.84 116.46 6,173.16 7,306.14 13,826.84   0.00             # ## #
2008 4 1 2008 Apr 1 0 13,826.84 264.30 264.30   0.00 149.08 115.22 6,322.24 7,421.36 13,677.76   0.00             # ## #
2008 5 1 2008 May 1 0 13,677.76 264.30 264.30   0.00 150.32 113.98 6,472.56 7,535.34 13,527.44   0.00             # ## #
2008 6 1 2008 Jun 1 0 13,527.44 264.30 264.30   0.00 151.57 112.73 6,624.13 7,648.07 13,375.87   0.00             # ## #
2008 7 1 2008 Jul 1 0 13,375.87 264.30 264.30   0.00 152.83 111.47 6,776.96 7,759.54 13,223.04   0.00             # ## #
2008 8 1 2008 Aug 1 0 13,223.04 264.30 264.30   0.00 154.11 110.19 6,931.07 7,869.73 13,068.93   0.00             # ## #
2008 9 1 2008 Sep 1 0 13,068.93 264.30 264.30   0.00 155.39 108.91 7,086.46 7,978.64 12,913.54   0.00             # ## #
2008 10 1 2008 Oct 1 0 12,913.54 264.30 264.30   0.00 156.69 107.61 7,243.15 8,086.25 12,756.85   0.00             # ## #
2008 11 1 2008 Nov 1 0 12,756.85 264.30 264.30   0.00 157.99 106.31 7,401.14 8,192.56 12,598.86   0.00             # ## #
2008 12 1 2008 Dec 1 0 12,598.86 264.30 264.30   0.00 159.31 104.99 7,560.45 8,297.55 12,439.55   0.00             # ## #
2009 1 1 2009 Jan 1 0 12,439.55 264.30 264.30   0.00 160.64 103.66 7,721.09 8,401.21 12,278.91   0.00             # ## #
2009 2 1 2009 Feb 1 0 12,278.91 264.30 264.30   0.00 161.98 102.32 7,883.07 8,503.53 12,116.93   0.00             # ## #
2009 3 1 2009 Mar 1 0 12,116.93 264.30 264.30   0.00 163.33 100.97 8,046.40 8,604.50 11,953.60   0.00             # ## #
2009 4 1 2009 Apr 1 0 11,953.60 264.30 264.30   0.00 164.69 99.61 8,211.09 8,704.11 11,788.91   0.00             # ## #
2009 5 1 2009 May 1 0 11,788.91 264.30 264.30   0.00 166.06 98.24 8,377.15 8,802.35 11,622.85   0.00             # ## #
2009 6 1 2009 Jun 1 0 11,622.85 264.30 264.30   0.00 167.44 96.86 8,544.59 8,899.21 11,455.41   0.00             # ## #
2009 7 1 2009 Jul 1 0 11,455.41 264.30 264.30   0.00 168.84 95.46 8,713.43 8,994.67 11,286.57   0.00             # ## #
2009 8 1 2009 Aug 1 0 11,286.57 264.30 264.30   0.00 170.25 94.05 8,883.68 9,088.72 11,116.32   0.00             # ## #
2009 9 1 2009 Sep 1 0 11,116.32 264.30 264.30   0.00 171.66 92.64 9,055.34 9,181.36 10,944.66   0.00             # ## #
2009 10 1 2009 Oct 1 0 10,944.66 264.30 264.30   0.00 173.09 91.21 9,228.43 9,272.57 10,771.57   0.00             # ## #
2009 11 1 2009 Nov 1 0 10,771.57 264.30 264.30   0.00 174.54 89.76 9,402.97 9,362.33 10,597.03   0.00             # ## #
2009 12 1 2009 Dec 1 0 10,597.03 264.30 264.30   0.00 175.99 88.31 9,578.96 9,450.64 10,421.04   0.00             # ## #
2010 1 1 2010 Jan 1 0 10,421.04 264.30 264.30   0.00 177.46 86.84 9,756.42 9,537.48 10,243.58   0.00             # ## #
2010 2 1 2010 Feb 1 0 10,243.58 264.30 264.30   0.00 178.94 85.36 9,935.36 9,622.84 10,064.64   0.00             # ## #
2010 3 1 2010 Mar 1 0 10,064.64 264.30 264.30   0.00 180.43 83.87 10,115.79 9,706.71 9,884.21   0.00             # ## #
2010 4 1 2010 Apr 1 0 9,884.21 264.30 264.30   0.00 181.93 82.37 10,297.72 9,789.08 9,702.28   0.00             # ## #
2010 5 1 2010 May 1 0 9,702.28 264.30 264.30   0.00 183.45 80.85 10,481.17 9,869.93 9,518.83   0.00             # ## #
2010 6 1 2010 Jun 1 0 9,518.83 264.30 264.30   0.00 184.98 79.32 10,666.15 9,949.25 9,333.85   0.00             # ## #
2010 7 1 2010 Jul 1 0 9,333.85 264.30 264.30   0.00 186.52 77.78 10,852.67 10,027.03 9,147.33   0.00             # ## #
2010 8 1 2010 Aug 1 0 9,147.33 264.30 264.30   0.00 188.07 76.23 11,040.74 10,103.26 8,959.26   0.00             # ## #
2010 9 1 2010 Sep 1 0 8,959.26 264.30 264.30   0.00 189.64 74.66 11,230.38 10,177.92 8,769.62   0.00             # ## #
2010 10 1 2010 Oct 1 0 8,769.62 264.30 264.30   0.00 191.22 73.08 11,421.60 10,251.00 8,578.40   0.00             # ## #
2010 11 1 2010 Nov 1 0 8,578.40 264.30 264.30   0.00 192.81 71.49 11,614.41 10,322.49 8,385.59   0.00             # ## #
2010 12 1 2010 Dec 1 0 8,385.59 264.30 264.30   0.00 194.42 69.88 11,808.83 10,392.37 8,191.17   0.00             # ## #
2011 1 1 2011 Jan 1 0 8,191.17 264.30 264.30   0.00 196.04 68.26 12,004.87 10,460.63 7,995.13   0.00             # ## #
2011 2 1 2011 Feb 1 0 7,995.13 264.30 264.30   0.00 197.67 66.63 12,202.54 10,527.26 7,797.46   0.00             # ## #
2011 3 1 2011 Mar 1 0 7,797.46 264.30 264.30   0.00 199.32 64.98 12,401.86 10,592.24 7,598.14   0.00             # ## #
2011 4 1 2011 Apr 1 0 7,598.14 264.30 264.30   0.00 200.98 63.32 12,602.84 10,655.56 7,397.16   0.00             # ## #
2011 5 1 2011 May 1 0 7,397.16 264.30 264.30   0.00 202.66 61.64 12,805.50 10,717.20 7,194.50   0.00             # ## #
2011 6 1 2011 Jun 1 0 7,194.50 264.30 264.30   0.00 204.35 59.95 13,009.85 10,777.15 6,990.15   0.00             # ## #
2011 7 1 2011 Jul 1 0 6,990.15 264.30 264.30   0.00 206.05 58.25 13,215.90 10,835.40 6,784.10   0.00             # ## #
2011 8 1 2011 Aug 1 0 6,784.10 264.30 264.30   0.00 207.77 56.53 13,423.67 10,891.93 6,576.33   0.00             # ## #
2011 9 1 2011 Sep 1 0 6,576.33 264.30 264.30   0.00 209.50 54.80 13,633.17 10,946.73 6,366.83   0.00             # ## #
2011 10 1 2011 Oct 1 0 6,366.83 264.30 264.30   0.00 211.24 53.06 13,844.41 10,999.79 6,155.59   0.00             # ## #
2011 11 1 2011 Nov 1 0 6,155.59 264.30 264.30   0.00 213.00 51.30 14,057.41 11,051.09 5,942.59   0.00             # ## #
2011 12 1 2011 Dec 1 0 5,942.59 264.30 264.30   0.00 214.78 49.52 14,272.19 11,100.61 5,727.81   0.00             # ## #
2012 1 1 2012 Jan 1 0 5,727.81 264.30 264.30   0.00 216.57 47.73 14,488.76 11,148.34 5,511.24   0.00             # ## #
2012 2 1 2012 Feb 1 0 5,511.24 264.30 264.30   0.00 218.37 45.93 14,707.13 11,194.27 5,292.87   0.00             # ## #
2012 3 1 2012 Mar 1 0 5,292.87 264.30 264.30   0.00 220.19 44.11 14,927.32 11,238.38 5,072.68   0.00             # ## #
2012 4 1 2012 Apr 1 0 5,072.68 264.30 264.30   0.00 222.03 42.27 15,149.35 11,280.65 4,850.65   0.00             # ## #
2012 5 1 2012 May 1 0 4,850.65 264.30 264.30   0.00 223.88 40.42 15,373.23 11,321.07 4,626.77   0.00             # ## #
2012 6 1 2012 Jun 1 0 4,626.77 264.30 264.30   0.00 225.74 38.56 15,598.97 11,359.63 4,401.03   0.00             # ## #
2012 7 1 2012 Jul 1 0 4,401.03 264.30 264.30   0.00 227.62 36.68 15,826.59 11,396.31 4,173.41   0.00             # ## #
2012 8 1 2012 Aug 1 0 4,173.41 264.30 264.30   0.00 229.52 34.78 16,056.11 11,431.09 3,943.89   0.00             # ## #
2012 9 1 2012 Sep 1 0 3,943.89 264.30 264.30   0.00 231.43 32.87 16,287.54 11,463.96 3,712.46   0.00             # ## #
2012 10 1 2012 Oct 1 0 3,712.46 264.30 264.30   0.00 233.36 30.94 16,520.90 11,494.90 3,479.10   0.00             # ## #
2012 11 1 2012 Nov 1 0 3,479.10 264.30 264.30   0.00 235.31 28.99 16,756.21 11,523.89 3,243.79   0.00             # ## #
2012 12 1 2012 Dec 1 0 3,243.79 264.30 264.30   0.00 237.27 27.03 16,993.48 11,550.92 3,006.52   0.00             # ## #
2013 1 1 2013 Jan 1 0 3,006.52 264.30 264.30   0.00 239.25 25.05 17,232.73 11,575.97 2,767.27   0.00             # ## #
2013 2 1 2013 Feb 1 0 2,767.27 264.30 264.30   0.00 241.24 23.06 17,473.97 11,599.03 2,526.03   0.00             # ## #
2013 3 1 2013 Mar 1 0 2,526.03 264.30 264.30   0.00 243.25 21.05 17,717.22 11,620.08 2,282.78   0.00             # ## #
2013 4 1 2013 Apr 1 0 2,282.78 264.30 264.30   0.00 245.28 19.02 17,962.50 11,639.10 2,037.50   0.00             # ## #
2013 5 1 2013 May 1 0 2,037.50 264.30 264.30   0.00 247.32 16.98 18,209.82 11,656.08 1,790.18   0.00             # ## #
2013 6 1 2013 Jun 1 0 1,790.18 264.30 264.30   0.00 249.38 14.92 18,459.20 11,671.00 1,540.80   0.00             # ## #
2013 7 1 2013 Jul 1 0 1,540.80 264.30 264.30   0.00 251.46 12.84 18,710.66 11,683.84 1,289.34   0.00             # ## #
2013 8 1 2013 Aug 1 0 1,289.34 264.30 264.30   0.00 253.56 10.74 18,964.22 11,694.58 1,035.78   0.00             # ## #
2013 9 1 2013 Sep 1 0 1,035.78 264.30 264.30   0.00 255.67 8.63 19,219.89 11,703.21 780.11   0.00             # ## #
2013 10 1 2013 Oct 1 0 780.11 264.30 264.30   0.00 257.80 6.50 19,477.69 11,709.71 522.31   0.00             # ## #
2013 11 1 2013 Nov 1 0 522.31 264.30 264.30   0.00 259.95 4.35 19,737.64 11,714.06 262.36   0.00             # ## #
2013 12 1 2013 Dec 1 0 262.36 264.30 264.30   0.00 262.11 2.19 19,999.75 11,716.25 0.25   0.00             # ## #
                                                       
                                                       
                                                       
      YEARLY SCHEDULE OF BALANCES AND PAYMENTS                                    
              Beginning             Cumulative Cumulative Ending                      
        Year     Balance   Payments     Principal Interest Principal Interest Balance                      
        2005     18,773.18   3,171.60     1,355.42 1,816.18 2,582.24 3,760.96 17,417.76                      
        2006     17,417.76   3,171.60     1,497.21 1,674.39 4,079.45 5,435.35 15,920.55                      
        2007     15,920.55   3,171.60     1,653.99 1,517.61 5,733.44 6,952.96 14,266.56                      
        2008     14,266.56   3,171.60     1,827.18 1,344.42 7,560.62 8,297.38 12,439.38                      
        2009     12,439.38   3,171.60     2,018.51 1,153.09 9,579.14 9,450.46 10,420.86                      
        2010     10,420.86   3,171.60     2,229.88 941.72 11,809.02 10,392.18 8,190.98                      
        2011     8,190.98   3,171.60     2,463.38 708.22 14,272.39 11,100.41 5,727.61                      
        2012     5,727.61   3,171.60     2,721.32 450.28 16,993.72 11,550.68 3,006.28                      
        2013     3,006.28   3,171.60     3,006.28 165.32 20,000.00 11,716.00 0.00