Need help creating formula that prorates a quarterly cost based on date

QueenQ

New Member
Joined
Apr 11, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have created a parking space rental lease that is based on a quarterly billing cycle. My issue is "stragglers" who wish to sign a lease after the quarter has started. Specifically, there are several lots. Each lot has it's own quarterly rate. I already have the form calculating the number of desired spots x quarterly cost then it calculates the total amount due from the customer. But how do I prorate this formula to acknowledge the effective date of the contract's impact on the rate? So, that the customer knows how much is due as of the effective date forward?

For example, the quarter is 4/1/22 - 6/30/22. If the person begins their lease 4/12/22, I need the formula to calculate the # of parking spots x the remaining time left during the current quarter.

Please help! I've tussled back and forth with this thing all afternoon!

Thanks
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
Map1.xlsm
AB
1normal start4/1/22
2normal stop6/30/22
3new start4/12/22
4normal price1.000,00 €
5new price879,12 €
Blad3
Cell Formulas
RangeFormula
B5B5=+B4*(B2-B3+1)/(B2-B1+1)
 

QueenQ

New Member
Joined
Apr 11, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thank you so very much! I'm not getting the proper figure though. Perhaps a snippet of my draft could help me better explain? I'm trying to make the prorated amount populate in column H based on the effective date of the initiated lease. Please see attachment.
 

Attachments

  • Snippet.JPG
    Snippet.JPG
    90.1 KB · Views: 5

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
How does column H knows that's a monthly or a quarterly rent ?
Can you insert a column (D?), where you add a "M" or a "Q", then in H with a formula, that amount can be calculated.
something like this ???
The green cells in L2:M3 calculate the beginning and end of the month or quarter with the date in E2. This cells, you can set them somewhere to the right, where they don't bother you.
The cell D5 is with data-validation, so you can only choose for M, Q, m, q or blank.
The cells E5:F5 have a custom numberformat to show the text behind that number. So you only have to enter the amount, excel adds the "/space", the numberformat is $ 0.00 " / space" (in the image here below, it's with a comma, because i have other regional settings)
Map3
ABCDEFGHIJKLM
1startend
2Effective Date4/13/2022month4/1/20224/30/2022
3quarter4/1/20226/30/2022
4number of spacesMonth/Quartermonth costQuarter CostAmount Due
51q$ 40,00 / space$ 120,00 / space$ 104,18
6
Blad1
Cell Formulas
RangeFormula
L2L2=EOMONTH(E2,-1)+1
M2M2=+EOMONTH(E2,0)
L3L3=DATE(YEAR(E2),FLOOR.MATH(MONTH(E2)-1,3)+1,1)
M3M3=+EDATE(L3,3)-1
G5G5=IF(D5="",0,IF(D5="M",($M$2-$E$2+1)/($M$2-$L$2+1)*E5,($M$3-$E$2+1)/($M$3-$L$3+1)*F5))*B5
Cells with Data Validation
CellAllowCriteria
D5ListM;Q;m;q
 

Attachments

  • Schermafbeelding 2022-04-14 002101.png
    Schermafbeelding 2022-04-14 002101.png
    30.8 KB · Views: 1
Last edited:

Forum statistics

Threads
1,175,803
Messages
5,899,562
Members
434,785
Latest member
Mymaccrack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top