There isn't a way to calculate random payments....with out going through a redoing the am schedule every time you do it....if you make one extra payment a year your mortgage will be paid off in about 24 years on a 30 year note _________________ Cedric Kalvesmaki
***Professional Disclaimer***
While I am a Mortgage Professional, this advice is generic in nature only.
Posted: Mon Aug 25, 2008 9:21 pm Post subject: Calculate Mortgage Extra Payment Savings
Well, I'm trying to do a spreadsheet with each of my payments and a field for the extra payment amount. I have the principal, interest, etc but want to show a field that calculates how much I saved in interested when I make an extra payment. Is there no way to do that?
Welcome to the forum. I have to agree with Cedric that you should not make random extra payment on the principle. Contact the lender and make an agreement on which you will make the extra payments. Calculate how much to save by extra mortgage payments by using the calculator at http://www.mortgagefit.com/calculators/extra-payment.html
Hope it helps. Feel free to ask if you have any further questions.
As you probably know Larry's and Cedric's answer was not very "real world". For someone making additional principal payments, it's far more common to have varying payments -- $2,000 extra one month, nothing for the next 3 months, then $500, etc. Very simple Excel program:
1. Go with a standard amortization spreadsheet and add a column for recording extra principal applications. Obviously these amount affected the resulting principal balance.
2. Establish the "base line" by multiplying the FULL decimal term (use NPER) by the P&I to obtain the "scheduled total payments". Very important you do not use the "nominal term" since P&I is a two-decimal number (usually half rounded). For example with a $100K loan at 6.50% and a P&I of $632.07, the "exact term is 359.9965316 months and total scheduled payments is $227,543.01.
3. Throw in another column with the difference between the "scheduled total payments" and the running total of P&I plus additional principal payments.
4. Use whatever trigger you're comfortable with to identify when the actual principal balance hits zero (I usually use a IF(Bal >=0,0,1) and use VLOOKUP (in my example, 1) to see the "savings".
No time value of money but a useful 'incentivizer". Simple enough for you to write yourself but with all the cells, I can see why Internet sites keep if off their "use tools" portion. If you're lazy, I'm sure a local LO has this spreadsheet on the computer and will give you a copy.
I agree with you in the sense that the borrower may not be able to make the same amount of extra payment each month. But do lenders allow for it once the borrower informs him that he'll be paying extra? or does this vary from one lender to another?
The "conforming world" abhors PPPs. Well, actually Fannie tried a soft PPP a number of years back with the hope the MBS world would pay up for this feature. Big yawn and confusion so the experiment went away. At least with Ginnies they come up with cute names (I and II, wow!) and throw in two features to dazzle the street.
So with a conforming the borrower can choose if and when to make additional principal payments – no permission needed as long as the loan is current. This permitted those who would leave no stone unturned to "earn" a commission to invent the MMA – money merge account. Nothing really deceptive there since they're selling software not sponsoring a shell game. Not sure what this "advanced financial software" does beyond the simple Excel spreadsheet. I'm certain it has fancy graphics and does involve the HELOC "checking account". Never understood the logical jump in the need to use a HELOC versus what most folks do – a money market account where they earn rather than pay interest. Of course, the HELOC is needed so the pitchman can make a commission in addition to the fat price of the "software". Haven't heard a lot of promos on this lately. Either borrowers are smarter or, most likely, the dry up of the HELOC wholesale market has killed the market. It will be back; the most clever pitch since option ARMs.
Hope “mortgage” goes to the thread you suggested and hop on to the site of the one peddler who responded since (s)he will be able to see how well-paced the pitch is. Very, very slick video although it’s a bit condescended early on with the “gee look at all these payments you’ve made and the balance is down only to $X”. Sell to ignorance but, in fairness, many think in linear rather than exponential terms.
Since “mortgage” asked the question, my guess is the (s)he has gone past Arithmetic 101 and will at least think twice before spending hard earned money on a simple spreadsheet. Stick with the 6.5% example above and say the borrower makes a $5,000 principal payment with payment #1. The magical program will say you’ve “saved” $25,514 and the loan is paid off in 312 months. So a $6,000 (the latest figure I’ve seen for this nonsense) plus the $5,000 payment returns $25,514. Not bad.
Is this how someone who hasn’t slept through math class would approach the problem? Doubt it. Let’s ignore the one month difference between the $6K payment for the software and the $5K principal payment and say we’ve “invested” $11K. Where are our cash flow saving? Forgoing 48 payments 312 monthly payment of $632.07 from now (again let me slide one month). OK, now the discount rate; cost of capital is an ambiguous concept with an individual but let’s take 8% -- probably on the low side for someone with a 6.5% mortgage. The present value of those 48 payments is $25,890 but that’s in month 312. Discount back to the present time and that’s $3,256 in today’s dollars. But, wait, I’ve shelled out $11,000 in today’s dollars. Where’s the meat? Rhetorical question; it’s in the LO’s pocket.
The program does have a valid point on daily interest on HELOC versus monthly computation on the mortgage so riding the mortgage payment until (BUT NOT AFTER) the late charge date can be a small benefit. On the other hand, blow a late charge date and you’re behind the eight ball.
I’m all in favor of accelerating payment of debt “all other things being equal” but I don’t see paying through the nose for a simple spreadsheet to do it. The spread sheet I’ve suggested should mirror the “MMA” bit at no cost. Figures are deceptive but if it encourages you to make additional principal payments, you’re likely ahead in the long run.
mortgage
