Notices

Mortgage Payoff question.

Old 05-15-2007, 02:28 PM
  #1  
Senior MemberCaptains Club Member
Thread Starter
 
Join Date: Sep 2001
Location: New Smyrna Beach, Florida and Aspen, Co
Posts: 3,640
Default Mortgage Payoff question.

I am trying to calculate a payoff on a mortgage that is privately held by a client and am pulling my hair out!!! Here are the terms.... December 2003, loan of $150,000 at 8% 20 year term simple interest with a payment of $1254.66 Payments are on time on the 1st of the month every month but here is the twist... My client recieved a payment on the principle in December of 2005 in the amount of $40,000 and another principle payment in December 2006 of $35,000..... The payments have continued on the first of every month up until April 1 at which time purchaser was intending to payoff the note. Any help figuring out the payoff as of May 31st????
Brett1 is offline  
Old 05-15-2007, 02:42 PM
  #2  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: Sep 2003
Location: Lancaster , RIC VA
Posts: 8,359
Default RE: Mortgage Payoff question.

To me this is essentially an IRR problem with periodic "cash flows" in addition to the P&I pmt and you need the FV for the payoff at 5/31/07.

How many on-time payment of $1254.66 in addition to the lump sum have been made? Did the lump sum reductions take the place of the normal P&I pmts?

To make it easier maybe you can calculate the balance/payoff at each lump sum reduction and the start the calculation again from there with the new balance, or find someone who remembers how to use an HP12C and enter the principle reductions to get the FV at period N on June 1.

You could get really ambitious and set up an excel spreadsheet to do this as well.

This reminds me of a Real Estate finance 300 level class exam question.
GWcpa is offline  
Old 05-15-2007, 02:44 PM
  #3  
Senior MemberCaptains Club Member
Thread Starter
 
Join Date: Sep 2001
Location: New Smyrna Beach, Florida and Aspen, Co
Posts: 3,640
Default Re: Mortgage Payoff question.

The lump sums took the place of the monthly payments for those respective months (Dec05,06)
Brett1 is offline  
Old 05-15-2007, 02:46 PM
  #4  
Senior MemberCaptains Club Member
 
Join Date: Aug 2003
Location: Cypress, TX - USA
Posts: 851
Default Re: Mortgage Payoff question.

roughly 69k is the principle balance

150k-40k-35k=75k
1255x5 (Jan-May)=6275
6275*.08=502
6275-502=5773
75k-5773=69227
Cat O Lies is offline  
Old 05-15-2007, 02:51 PM
  #5  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: Sep 2003
Location: Lancaster , RIC VA
Posts: 8,359
Default Re: Mortgage Payoff question.

Ok, so a portion of the lumps were interest.

8% Balance
Period pmt interest principle Total P&I 150,000.00
1 1254.66 1,000.00 254.66 1,254.66 149,745.34
2 1254.66 998.30 256.36 1,254.66 149,488.98
3 1254.66 996.59 258.07 1,254.66 149,230.92
4 1254.66 994.87 259.79 1,254.66 148,971.13
5 1254.66 993.14 261.52 1,254.66 148,709.61
6 1254.66 991.40 263.26 1,254.66 148,446.35
7 1254.66 989.64 265.02 1,254.66 148,181.33
8 1254.66 987.88 266.78 1,254.66 147,914.54
9 1254.66 986.10 268.56 1,254.66 147,645.98
10 1254.66 984.31 270.35 1,254.66 147,375.63
11 1254.66 982.50 272.16 1,254.66 147,103.47
12 1254.66 980.69 273.97 1,254.66 146,829.50
13 1254.66 978.86 275.80 1,254.66 146,553.71
14 1254.66 977.02 277.64 1,254.66 146,276.07
15 1254.66 975.17 279.49 1,254.66 145,996.58
16 1254.66 973.31 281.35 1,254.66 145,715.23
17 1254.66 971.43 283.23 1,254.66 145,432.01
18 1254.66 969.55 285.11 1,254.66 145,146.90
19 1254.66 967.65 287.01 1,254.66 144,859.88
20 1254.66 965.73 288.93 1,254.66 144,570.95
21 1254.66 963.81 290.85 1,254.66 144,280.10
22 1254.66 961.87 292.79 1,254.66 143,987.31
23 1254.66 959.92 294.74 1,254.66 143,692.56
24 1254.66 957.95 296.71 1,254.66 143,395.85
25 1254.66 955.97 298.69 1,254.66 143,097.17
26 1254.66 953.98 300.68 1,254.66 142,796.49
27 1254.66 951.98 302.68 1,254.66 142,493.80
28 1254.66 949.96 304.70 1,254.66 142,189.10
29 1254.66 947.93 306.73 1,254.66 141,882.37
30 1254.66 945.88 308.78 1,254.66 141,573.59
31 1254.66 943.82 310.84 1,254.66 141,262.76
32 1254.66 941.75 312.91 1,254.66 140,949.85
33 1254.66 939.67 314.99 1,254.66 140,634.85
34 1254.66 937.57 317.09 1,254.66 140,317.76
35 1254.66 935.45 319.21 1,254.66 139,998.55
36 1254.66 933.32 321.34 1,254.66 139,677.21
37 1254.66 931.18 323.48 1,254.66 139,353.74
38 1254.66 929.02 325.64 1,254.66 139,028.10
39 1254.66 926.85 327.81 1,254.66 138,700.30
40 1254.66 924.67 329.99 1,254.66 138,370.30
41 1254.66 922.47 332.19 1,254.66 138,038.11
42 1254.66 920.25 334.41 1,254.66 137,703.71
43 1254.66 918.02 336.64 1,254.66 137,367.07
44 1254.66 915.78 338.88 1,254.66 137,028.19
45 1254.66 913.52 341.14 1,254.66 136,687.05
46 1254.66 911.25 343.41 1,254.66 136,343.64




I just blasted this into a spreadsheet on the fly. If you can determine which period the lumps were made, I think you can enter it into the above and get a balance.

The numbers will probably be a mess when I post it.

Of course I may be way out on this.



Oh, I stopped at 46 for no reason, I just pulled down and copied cells and stopped for no specific reason.
GWcpa is offline  
Old 05-15-2007, 03:06 PM
  #6  
Senior MemberCaptains Club Member
 
Join Date: Aug 2006
Location: Central Florida
Posts: 8,225
Default Re: Mortgage Payoff question.

I can do this accurately. What was the date the loan was made?
Sprockets is offline  
Old 05-15-2007, 03:07 PM
  #7  
Senior MemberCaptains Club Member
 
Join Date: Aug 2006
Location: Central Florida
Posts: 8,225
Default Re: Mortgage Payoff question.

I can do this accurately. What was the date the loan was made?
Sprockets is offline  
Old 05-15-2007, 03:07 PM
  #8  
Senior MemberCaptains Club Member
 
Join Date: Aug 2003
Location: Cypress, TX - USA
Posts: 851
Default Re: Mortgage Payoff question.

oh, i must be on crack today! he said the 40kand 35K pymts were princ. and int. Monday residual is still working strong!
Cat O Lies is offline  
Old 05-15-2007, 03:10 PM
  #9  
Senior MemberCaptains Club Member
 
Join Date: Jul 2006
Location: Huntington, NY
Posts: 974
Default Re: Mortgage Payoff question.

also type online for mortgage payment calculator. Or amorization chart. There are some very good spreadsheets online if you can find them
wellcraft290 is offline  
Old 05-15-2007, 03:10 PM
  #10  
Senior MemberCaptains Club Member
 
Join Date: Aug 2003
Location: Cypress, TX - USA
Posts: 851
Default Re: Mortgage Payoff question.

http://www.bankrate.com/brm/amortiza...tization+Table
Cat O Lies is offline  
Old 05-15-2007, 03:12 PM
  #11  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: May 2005
Posts: 6,194
Default Re: Mortgage Payoff question.

Here is the calc, which assumes that the 35K and 40K were Principal only and the regular P&I was paid seperately. Also assumes the 35 and 40K were paid at the same time as the P&I. To do this accurately you weed need to know the exact date of the pre-payments for 35 and 40K.

RI Builder is offline  
Old 05-15-2007, 03:20 PM
  #12  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: Sep 2003
Location: Lancaster , RIC VA
Posts: 8,359
Default Re: Mortgage Payoff question.

Good answer above. That's what they have software for.

But I think a portion of the lumps cover the accrued interest as well and were not in addition to the normal P&I.
GWcpa is offline  
Old 05-15-2007, 03:21 PM
  #13  
Senior MemberCaptains Club Member
 
Join Date: Aug 2006
Location: Central Florida
Posts: 8,225
Default Re: Mortgage Payoff question.

Assuming the loan was made on 12/1/2003, the payoff on May 31, 2007 comes to $61,584.86. This is based on actual days, with interest compounded daily. It also takes into account that part of the 2 large payments were allocated to interest.
Sprockets is offline  
Old 05-15-2007, 03:22 PM
  #14  
Senior MemberCaptains Club Member
 
Join Date: Aug 2003
Location: Cypress, TX - USA
Posts: 851
Default Re: Mortgage Payoff question.

my brain hurts
Cat O Lies is offline  
Old 05-15-2007, 03:27 PM
  #15  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: May 2005
Posts: 6,194
Default Re: Mortgage Payoff question.

GWcpa - 5/15/2007 3:20 PM

Good answer above. That's what they have software for.

But I think a portion of the lumps cover the accrued interest as well and were not in addition to the normal P&I.
If a portion of the 35 and 40K covered the regularly scheduled payment as well, it is revised as follows (FYI - this is just an excel spreadsheet that I built)

Boatless Again - your calc does not seem to capture accrued interest earned, but not paid - the client is still owed interest for March, April and May (assuming end of May payoff). The way I read the original post, last payment was made on March 1, which would have covered February.

RI Builder is offline  
Old 05-15-2007, 03:44 PM
  #16  
Senior MemberCaptains Club Member
 
Join Date: Aug 2006
Location: Central Florida
Posts: 8,225
Default Re: Mortgage Payoff question.

RI - I assumed the April 1, 2007 payment was made. The only other difference I can see is that my software uses actual days, while your spreadsheet uses a 360 day year. It just shows how complex a relatively simple calculation can get. I use an old software program that allows me to enter a specific stream of payments by date and the rate can change on every payment.
Sprockets is offline  
Old 05-15-2007, 03:48 PM
  #17  
Senior MemberCaptains Club Member
PLEDGER
 
HOTSPOT's Avatar
 
Join Date: Aug 2003
Location: Bradenton, FL
Posts: 13,395
Default Re: Mortgage Payoff question.

I need a drank.
HOTSPOT is offline  
Old 05-15-2007, 03:49 PM
  #18  
Senior MemberCaptains Club MemberPLEDGER
 
Join Date: May 2005
Posts: 6,194
Default Re: Mortgage Payoff question.

Boatless:

Agreed. I just use 30/360 for simplicity and is the basis I use when I hold notes for clients. Either way, we can ascertain that the payoff is in the neighborhood of $62K
RI Builder is offline  
Old 05-15-2007, 04:12 PM
  #19  
Senior MemberCaptains Club Member
 
Join Date: Nov 2002
Location: Jones Inlet, NY
Posts: 157
Default Re: Mortgage Payoff question.

Sounds like d-hlaw owes RI Builder and Boatless some sort of consultation fee...


flattie is offline  
Old 05-15-2007, 04:29 PM
  #20  
Senior MemberCaptains Club Member
 
Join Date: Aug 2006
Location: Central Florida
Posts: 8,225
Default RE: Mortgage Payoff question.

Looks like a nice boat to fish from and not far from home
Sprockets is offline  

Thread Tools
Search this Thread