I have a mortgage problem that I simply cannot seem to figure out.
Scenario - I started doodling
with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and
the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial
institution will "pay" this shortfall from a previously agreed upon additional amount available on the property -
While there is a shortfall between the income generated by the property and the bond payment,
insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall
is added onto the outstanding capital (loan at the bank).
Once the shortfall turns into surplusses, the
surplusses are in turn paid into the account until the bond is paid in full.
With a normal, say 15 year, bond, the
interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a
balance of 0 in month 180, but not in the scenario I describe.
What happens in the scenario I described above is
that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each
month's deficit is added to the outstanding capital but the payment stays constant.
I want this payment to
remain constant throughout the bond.
The outstanding capital gradually increases as deficits are added (the idea
is not to have to pay anything out of my pocket into the bond while there is a shortfall).
Then, as the annual
rent increases over the next few years, the deficit gets less and less until it turns positive. This positive figure is also
added (actually, "deducted") to the outstanding capital and apart from the standard capital portion of the PMT function,
lowers the outstanding capital even more.
All of the above causes the bons to be paid in full before the actual
initial term is finished.
This is great until I tried getting the bond to actually finish on the date it was
supposed to finish, despite all the additions (the deficits) to the outstanding capital and the subtractions (the surplusses)
from the same.
I want to keep the payment the same throughout the bond term, assume a constant interest rate is
charged and assume a constant net increase in rentals is achieved.
How do I calculate a constant payment for a
given term and have it work out to 0 at the end of that term, given the aforementioned standards?
Let's hear your
clever ideas, please!
P.S. The attachment is very basic due to size restrictions. Please drag row A24:M24 down up to 240 months in column A (20