0

Formula for calculate total of recurring expenses

Hi, I've been looking but can't find a simple solution to my problem. I have recurring expenses recorded in records where there is a start date and an end date. For example: subscription to xyz beginning 2021/05/01 end 2022/01/10. These charges are entered only once but I would like them to be calculated for all the months of the duration. So I would like to get a formula where it gives me a result of the price multiplied by the months of duration from the start date to the end date. How can I do? Thank you

 

TRANSLATE with x English Arabic Hebrew Polish Bulgarian Hindi Portuguese Catalan Hmong Daw Romanian Chinese Simplified Hungarian Russian Chinese Traditional Indonesian Slovak Czech Italian Slovenian Danish Japanese Spanish Dutch Klingon Swedish English Korean Thai Estonian Latvian Turkish Finnish Lithuanian Ukrainian French Malay Urdu German Maltese Vietnamese Greek Norwegian Welsh Haitian Creole Persian     TRANSLATE with COPY THE URL BELOW Back EMBED THE SNIPPET BELOW IN YOUR SITE Enable collaborative features and customize widget: Bing Webmaster Portal Back

4 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Giuseppe -

     

    This is an interesting question. So I was able to create the following table.

    Screen Shot 2022-01-21 at 11.23.11

     

    There are two scenarios, that come to mind, that you have to account for:

     

    1) what do you do with ongoing subscriptions

    2) what about subscriptions that are multi year

     

    So here is one possibility: (change all field names to match your DB)

     

    let sYear := year(Start); <--gets the year from the Start date field
    let eYear := year(End); <--gets the year from the End date field
    if eYear = null then<--if eYear is null then this is an ongoing subscription
         let curYear := year(today());<--gathers the current year
         if curYear = sYear then<--checks to see if current year matches the start year
            let xMon := month(today()) - month(Start) + 1;<--simple equation, add 1 so it includes the first month
            MonthlyFee * xMon<--amount paid so far
         else<--this is when the current year is greater than start year
            let xYear := year(today()) - sYear;<--first we figure out how many years have passed
            let xMon := 12 * xYear + month(today()) - month(Start) + 1 ;<--here we figure out how many months have passes
            MonthlyFee * xMon<--now we figure out how much we have paid
         end
    else<--this is when there is an end date
         if sYear = eYear then<--check to see if the end Year and start Year are in the same year
            let xMon := month(End) - month(Start) + 1;
            MonthlyFee * xMon
         else<--this is what happens when they are not
            let xYear := eYear - sYear;
            let xMon := 12 * xYear + month(End) - month(Start) + 1;
            MonthlyFee * xMon
         end
    end

     

    I hope this gives you an idea on how to approach it. Let us know how it goes.

    • giuseppe
    • 2 yrs ago
    • Reported - view

    Thank you Fred!!! You have been very kind and very detailed. Now I will try to put the formula together and be able to test it. Given your expertise, let me ask you one last question. In your opinion is it possible, in this scenario, to get a field that can only give me the recurring expenses of the current year? The basic fact is that I'm trying to create an expense report and having the figure year after year would really be ideal. Thanks again for your help

     

    TRANSLATE with x English Arabic Hebrew Polish Bulgarian Hindi Portuguese Catalan Hmong Daw Romanian Chinese Simplified Hungarian Russian Chinese Traditional Indonesian Slovak Czech Italian Slovenian Danish Japanese Spanish Dutch Klingon Swedish English Korean Thai Estonian Latvian Turkish Finnish Lithuanian Ukrainian French Malay Urdu German Maltese Vietnamese Greek Norwegian Welsh Haitian Creole Persian     TRANSLATE with COPY THE URL BELOW Back EMBED THE SNIPPET BELOW IN YOUR SITE Enable collaborative features and customize widget: Bing Webmaster Portal Back
    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Guiseppe -

     

    Most things are possible. I'll give just one example. Here is what I've come up with:

    Screen Shot 2022-01-22 at 09.39.35

     

    If we take our previous code and put it in a new field, which I call Exp2020, and we will try to figure out how much a service costed you in 2020 you can do:

     

    let sYear := year(Start);
    let eYear := year(End);
    let checkS2020 := if sYear <= 2020 then 1 end;<--checks to see if a service started in 2020 and assigns a 1
    let checkE2020 := if eYear >= 2020 or eYear = null then<--checks to see if a service ended in 2020 or is still ongoing then assigns a 1
    1
    end;
    let check2020 := checkS2020 + checkE2020;<--adds the two variables above, so we only want the follow formula to run when this variable equals 2.
    switch check2020 do<--we add a switch command so we don't have to add another if statement
    case 2:<--we only provide a formula when the variable equals 2, so the formula has been simplified
       if sYear = 2020 then<--if the service started in 2020
         let xMon := 12 - month(Start) + 1;
         MonthlyFee * xMon
       else<--the service has been running for the whole year
         MonthlyFee * 12
       end
    end

    • giuseppe
    • 2 yrs ago
    • Reported - view

Content aside

  • 2 yrs agoLast active
  • 4Replies
  • 162Views