0

The context for this question is that say I have 200 dollars locked up that will be paid back to me each month for a varying amount of money with the final payment taking pace on the 4th year. I'm NOT trying to determine the present value of the 200 dollars BUT rather the weighted average "lockup period".

i.e.

1/31/2023 pay back $1 dollar 2/28/2023 pay back $0.5 ...... 1/31/2027 pay back remaining $0.3

How do I calculate the weighted average of the "lock-up period" because intuitively since the money is being paid back to me, the lock up time period is not 4 years but rather should be weighted by the $ amount being paid back.

Should it be simply be: =sumproduct(Every date: Every $amount)/ sum(Every $amount)?

Improve this question
2
  • 1
    This seems like more of a math question than an Excel or programming question. Did you run into some problem with the formula you suggested using? What is your expected result? Commented May 4, 2024 at 5:27
  • In this example, the first and last payments are both on 31st January. If the first payment is in January of year 1, the last payment would normally be in December of year 4 (not January of year 5). This doesn't affect your underlying question but I mention it because it is a common source of error. Commented May 4, 2024 at 10:28

1 Answer 1

Reset to default
0

I think what you are asking about is Weighted Average Life (WAL). See this link: https://www.investopedia.com/terms/w/weightedaveragelife.asp

The WAL is, loosely, the average time to maturity per unit money.

This being the case, your calculation is correct. If you have the repayment amounts in percentages, you can simplify by just doing a SUMPRODUCT() over the dates and repayment percentages. (Interest is ignored.)

A couple of caveats:

  • Beware using Excel dates in calculations like this. In some cases, it is safer to convert to a time span in days (from a start date) before performing any calculations.

  • Be careful of UK/US date format confusion, especially if sharing this sheet internationally.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for confirming that. Wouldn't it give me the same result if I used the dollar amount per period ? Please see my calc below docs.google.com/spreadsheets/d/…
I'm not quite sure what you mean. Your spreadsheet looks correct, and corresponds with the formula in your question. The benefit of using percentages is that it's a simple SUMPRODUCT() (no average required), and yes, the results will be the same. If you don't have the percentages already then your formula is simpler, because calculating the percentages would effectively require repeated divisions by the total sum (instead of just a single division after the SUMPRODUCT()).
My apologies for the confusion I meant instead of using dates and repayment percentages, I used the per period payment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.