Prorating Value by Month (Based on Start and End Date)

Copper Contributor

Hello,

 

I'm hoping the Microsoft community can help me solve this problem that I simply cannot wrap my head around as to how to create the formula I need to solve the issue.

 

I have an annual value that has already been prorated if the start date is shortened and is not starting on the first day of the year, otherwise its the full year annual value, the value then accounts for projection, so it increases in 2025, and 2026.

 

I'm trying to create a summary that will show what the month to month value is (in columns Jan, Feb, Mar, etc..) taking into account the start and end date.

Link to Spreadsheet: 

https://1drv.ms/x/s!ApO5yBVbz-BDyFOUz7aR09FxyXvc?e=0Fjjyt 

 

table.png

 

I tried to use the following formula, however it relies on the input value being monthly, and did not work for future start/end dates for some reason. (Would blank out with no value).

 

ff.PNG

1 Reply

@AmanD90 

 

Thanks for making your original file available on OneDrive. I've looked at it, and need to ask a question or two before trying to answer. I notice (as you no doubt have as well) that you've had 80 some views but no replies. I suspect it's because what you've given us raises questions like these:

  1. What are the initial not pro-rated numbers? (it would help clarity if you had a column before the two Start and End date columns that showed that.)
  2. Why, for a number that appears to start and end within 2024 are there any values at all showing in columns D and E, for 2025 and 2026 respectively? That contributes to some of the confusion here.
  3. Similarly, why does the line for a number that begins and ends in 2025 show a pro-rated number in 2026?
  4. And maybe the biggest unanswered, underlying question of them all: WHAT is this all about anyway? What are we pro-rating? Balances? Payments? Withdrawals? It always helps to have the bigger picture, the framework within which you're trying to use Excel,, the purpose of the spreadsheet in the first place.