formula for weighted average life of mortgage

[tonywatsonhelp]

Hi Everyone,
Was hoping someone might have an answer to this one as it seems to be one of the great misteries of the internet.

So I need a real accurate calculation for the weighted average life of a mortgage where the amounts paid each month are know but variable.

assume the period is 25years, how do I calculate the weighted average life?

thanks

Tony

 

[Stiuart_W]

Sorry I can't help. I've never heard of "weighted average life" as applied to a mortgage.

In the UK the vast majority of mortgage loans (all?) are sold as a fixed term (life) so it is always knows, usually 25 years but can be shorter or longer as agreed with the lender.


Perhaps you are asking something else. The moneysavingexpert forum has a good (free) mortgage calculator

 

[tonywatsonhelp]

Oh I didn't realise it was ok to go on posts and tell people you can't help them!

To think all those posts I've read where I didn't know the answer, I could have posted that! Then they would know I don't know, then when they turn around in there cars and drive back 40 minutes home to read the reply, they will be about as please as I am right now that I drove back home for 40 minutes just read someone can't help me because they don't know the answer!!!!!!!!!!!!!

 

[joeu2004]

So I need a real accurate calculation for the weighted average life of a mortgage where the amounts paid each month are know but variable. assume the period is 25years, how do I calculate the weighted average life?

For the definition of WAL, see http://en.wikipedia.org/wiki/Weighted-average_life.

If payments were equal, we might calculate WAL as follows:

=SUMPRODUCT(PPMT(A1/12,ROW(INDIRECT("1:"&12*25)),12*25,-A2), ROW(INDIRECT("1:"&12*25)))
/ 12 / A2

where A1 is the annual interest rate, and A2 is the total principal.

Note: That is not an efficient formula because INDIRECT is a volatile function. The point is to demonstrate the concept.

But if payments are variable, it is difficult to compute (or know) the amount of principal repaid with each payment. I would set up the following amortization schedule.

A1:       annual interest rate
A2:       total principal
E1:       =A2               (initial balance)
B2:B301:  payment number (1 through 300)
C2:C301:  payment for each period
D2:       =C2-E1*$A$1/12    (principal repaid in each payment)
E2:       =E1-D2            (balance after payment)
copy D2:E2 down through D301:E301

Then we might calculate WAL as follows:

=SUMPRODUCT(D2:D301,B2:B301) / 12 / A2

We might be able to avoid the amortization schedule (TBD) if the payments vary monthly in a deterministic way, e.g. x% per month. I suspect that is not the case.

For my edification, please explain how the payments vary and why, if you know. Just curious.

 

[Stiuart_W]

Oh I didn't realise it was ok to go on posts and tell people you can't help them!

To think all those posts I've read where I didn't know the answer, I could have posted that! Then they would know I don't know, then when they turn around in there cars and drive back 40 minutes home to read the reply, they will be about as please as I am right now that I drove back home for 40 minutes just read someone can't help me because they don't know the answer!!!!!!!!!!!!!

Hi tony



Not sure the heavy sarcasm was called for and I can tell you I didn't appreciate it. Fair enough if I had just posted "I can't help", but I did post more than that and made a suggestion. You know, the reason I didn't understand the question might not have been my ignorance of the subject rather than your description of your problem (as it turned out it was my ignorance but not being psychic I wasn't to know that)

 

[joeu2004]

PS....

If payments were equal, we might calculate WAL as follows:

=SUMPRODUCT(PPMT(A1/12,ROW(INDIRECT("1:"&12*25)),12*25,-A2), ROW(INDIRECT("1:"&12*25)))
/ 12 / A2

where A1 is the annual interest rate, and A2 is the total principal.

My purpose in presenting that formula was to lay the groundwork for the second formula.

But as the wiki page explains, if payments are indeed equal (not Tony's situation), we can calculate WAL more simply as follows:

=(PMT(A1/12,25*12,-A2)*25*12 - A2) / (A2*A1)

where A1 is the annual interest rate, and A2 is the total principal.

The numerator calculates total interest.

 

[tonywatsonhelp]

Hi Stiuart,
My apologies, I was on my way out when I got an email on my phone saying I had a reply, because I couldn't read that reply on my phone and this was an important question I turned around and drove all the way home again which was 40 minutes in mind numbing traffic and blistering heat! (well Blistering for England anyway!)
So when I opened the reply to read it was someone telling me they didn't know the answer I was irritated because I needn't have come all the way home, and didn't understand the point of just telling me you didn't know the answer. but I'm sorry I replied like I did I will be more graceful in the future.

Tony

 

[really]

Hi Stiuart,
My apologies, I was on my way out when I got an email on my phone saying I had a reply, because I couldn't read that reply on my phone and this was an important question I turned around and drove all the way home again which was 40 minutes in mind numbing traffic and blistering heat! (well Blistering for England anyway!)
So when I opened the reply to read it was someone telling me they didn't know the answer I was irritated because I needn't have come all the way home, and didn't understand the point of just telling me you didn't know the answer. but I'm sorry I replied like I did I will be more graceful in the future.

Tony

To be fair, I was with you originally - I mean, why would anyone post "I don't know, try google"... but you drove 40 minutes (both ways really) in blistering heat to get an answer that is actually widely available on google? Do you not have air conditioning? Come on.