Excel file is: https://www.dropbox.com/s/93c2pfyzi03w1el/Income%20Yield.xlsx?dl=0
Sample data is below, There's a related table that associated the "Asset ID" with "Project"
Some of these Asset ID's will have an Income Yield in the future and some will be in the present. For example, if the Asset is scheduled to be purchased on 6/30/19 it will be in the future, but if the Asset was purchased on 10/31/18, it's in the past.
I'm trying to get a weighted average (by the Equity Deployed) of the Current income yield if the Asset has already been purchased, or the first period future income yield if the asset is going to be acquired in the future. In drafting this post, I realized that I could create a new calculated column called "Current Month Income Yield" which returns current month if asset purchase was in the past, and first future month if asset purchase is in the future, and then sumproduct that way, but I'm not sure how to create that calculated column.
| | Purchase Date | Equity Deployed | Current Month Income Yield | First Month Income Yield |
| A | 10/31/2018 | 5 | 2.50% | 1.75% |
| B | 2/28/2019 | 10 | 1.60% | 1.50% |
| C | 6/30/2019 | 7 | 2.00% | 2.00% |
Desired output would be: 1.93% and in Excel is calculated: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])
NumberAsset IDCountCalendar MonthEquity DeployedIncome Yield - Base (monthly)
| 1 | EC135 (MSN 0467) | 0 | 3/31/2019 | $1,260,000 | |
| 2 | EC135 (MSN 0467) | 1 | 4/1/2019 | $1,260,000 | 1.61% |
| 3 | EC135 (MSN 0467) | 2 | 5/1/2019 | $1,260,000 | 1.62% |
| 4 | EC135 (MSN 0467) | 3 | 6/1/2019 | $1,260,000 | 1.62% |
| 5 | EC135 (MSN 0467) | 4 | 7/1/2019 | $1,260,000 | 1.63% |
| 6 | EC135 (MSN 0467) | 5 | 8/1/2019 | $1,260,000 | 1.63% |
| 7 | EC135 (MSN 0467) | 6 | 9/1/2019 | $1,260,000 | 1.63% |
| 8 | EC135 (MSN 0467) | 7 | 10/1/2019 | $1,260,000 | 1.64% |
| 9 | EC135 (MSN 0467) | 8 | 11/1/2019 | $1,260,000 | 1.64% |
| 10 | EC135 (MSN 0467) | 9 | 12/1/2019 | $1,260,000 | 1.65% |
| 11 | EC135 (MSN 0467) | 10 | 1/1/2020 | $1,260,000 | 1.65% |
| 12 | EC135 (MSN 0467) | 11 | 2/1/2020 | $1,260,000 | 1.66% |
| 13 | EC135 (MSN 0467) | 12 | 3/1/2020 | $1,260,000 | 1.66% |
| 14 | EC135 (MSN 0467) | 13 | 4/1/2020 | $1,260,000 | 1.66% |
| 15 | EC135 (MSN 0467) | 14 | 5/1/2020 | $1,260,000 | 1.67% |
| 16 | EC135 (MSN 0467) | 15 | 6/1/2020 | $1,260,000 | 1.67% |
| 17 | EC135 (MSN 0467) | 16 | 7/1/2020 | $1,260,000 | 1.68% |
| 18 | EC135 (MSN 0467) | 17 | 8/1/2020 | $1,260,000 | 1.68% |
| 19 | EC135 (MSN 0467) | 18 | 9/1/2020 | $1,260,000 | 1.69% |
| 20 | EC135 (MSN 0467) | 19 | 10/1/2020 | $1,260,000 | 1.69% |
| 21 | EC135 (MSN 0467) | 20 | 11/1/2020 | $1,260,000 | 1.69% |
| 22 | EC135 (MSN 0467) | 21 | 12/1/2020 | $1,260,000 | 1.70% |
| 23 | EC135 (MSN 0467) | 22 | 1/1/2021 | $1,260,000 | 1.70% |
| 24 | EC135 (MSN 0467) | 23 | 2/1/2021 | $1,260,000 | 1.71% |
| 25 | EC135 (MSN 0467) | 24 | 3/1/2021 | $1,260,000 | 1.71% |
| 75 | EC135 (MSN 0472) | 0 | 3/31/2019 | $1,260,000 | |
| 76 | EC135 (MSN 0472) | 1 | 4/1/2019 | $1,260,000 | 1.61% |
| 77 | EC135 (MSN 0472) | 2 | 5/1/2019 | $1,260,000 | 1.62% |
| 78 | EC135 (MSN 0472) | 3 | 6/1/2019 | $1,260,000 | 1.62% |
| 79 | EC135 (MSN 0472) | 4 | 7/1/2019 | $1,260,000 | 1.63% |
| 80 | EC135 (MSN 0472) | 5 | 8/1/2019 | $1,260,000 | 1.63% |
| 81 | EC135 (MSN 0472) | 6 | 9/1/2019 | $1,260,000 | 1.63% |
| 82 | EC135 (MSN 0472) | 7 | 10/1/2019 | $1,260,000 | 1.64% |
