Weighted Average Life

[andrewmac]

Having a crisis of confidence here. I need to calculate the average
life of a loan (ignoring interest payments) that has no amortization
until maturity. The loan is for 5 million and matures after 3 years.
So I have this on my spreadsheet:


A B

Year Outstanding Balance
1 1 5,000,000
2 2 5,000,000
3 3 5,000,000

I am using the formula =SUMPRODUCT(B1:B3,A1:A3)/SUM(B1:B3)

The formula is returning 2.00 but the answer must surely be 3 as there
are no amortizations.

Whjat is wrong with my formula.

Any help much appreciated.

Thanks

Andrew

 

[Roger Govier]

Hi Andrew

The formula is returning the correct answer for the data provided.
The AVERAGE of 1 to 3 is 2

--

Regards
Roger Govier

Having a crisis of confidence here. I need to calculate the average
life of a loan (ignoring interest payments) that has no amortization
until maturity. The loan is for 5 million and matures after 3 years.
So I have this on my spreadsheet:


A B

Year Outstanding Balance
1 1 5,000,000
2 2 5,000,000
3 3 5,000,000

I am using the formula =SUMPRODUCT(B1:B3,A1:A3)/SUM(B1:B3)

The formula is returning 2.00 but the answer must surely be 3 as there
are no amortizations.

Whjat is wrong with my formula.

Any help much appreciated.

Thanks

Andrew

__________ Information from ESET Smart Security, version of virus
signature database 5216 (20100621) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5216 (20100621) __________

The message was checked by ESET Smart Security.

http://www.eset.com