How to Calculate MTD and YTD Values for General Ledger Data?

When data is loaded, Planful automatically calculates either Month-to-Date (MTD) or Year-to-Date (YTD) values based on the data load rule setting.

When used in the DLR, this is how the calculation happens:

• YTD setting: YTD=MTD current period + YTD prior period

• MTD setting: MTD=YTD current period - YTD prior period

• Account Based: If account type is Flow then YTD=MTD current period + YTD prior period. If account type is Balance then MTD=YTD current period - YTD prior period.

  

Here’s the complete explanation for calculating MTD and YTD values for GL data in Planful, including how to handle Flow and Balance accounts separately:

How the System Calculates MTD and YTD Values for GL Data?

When loading data, Planful automatically calculates either MTD (Month-to-Date) or YTD (Year-to-Date) values based on the data load rule setting and the Account Type (Flow or Balance).

1. For Flow Accounts (Revenue, Expenses): Flow accounts track cumulative activity over time (e.g., revenue, expenses, etc.).

• YTD Setting (Flow Accounts):

  • MTD = YTD (current period) - YTD (prior period)

  • This calculates MTD as the difference between the current period's YTD value and the prior period's YTD value.

• MTD Setting (Flow Accounts):

  • YTD = MTD (current period) + YTD (prior period)

  • This calculates YTD as the sum of the current period's MTD and the prior period's YTD values.  

    Let’s see an example for MTD and YTD:

    	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
    Exchange Rates	0.7518	0.7572	0.7478	0.7472	0.7432	0.7517
    Local Currency MTD	26,01,677	28,77,680	31,62,900	28,41,418	28,92,796	31,94,614
    Common Currency MTD	19,55,941	21,78,979	23,65,217	21,23,108	21,49,926	24,01,391
    Local Currency YTD	26,01,677	54,79,357	86,42,257	1,14,83,675	1,43,76,471	1,75,71,086
    Common Currency YTD	19,55,941	41,34,920	65,00,137	86,23,245	1,07,73,171	1,31,74,562

    
    Calculating Common Currency YTD (Flow Accounts):

    Multiply each month’s MTD local currency amount by the exchange rate for that month.

    • For Jul-24:

      • MTD local currency = 26,01,677

      • Exchange rate = 0.7518

      • Common Currency YTD = 26,01,677 *0.7518 = 19,55,941

    Calculating Local Currency MTD (Flow Accounts):

    • For Aug-24:

      • YTD Local Currency (Aug-24) =  54,79,357

      • YTD Local Currency (Jul-24) = 26,01,677

      • Local Currency MTD = 28,77,680

    Calculating Local Currency YTD (Flow Accounts):

    Add the previous month’s YTD CC and the current month’s MTD CC.

    • For Aug-24:

      • YTD Common Currency (Jul-24) = 19,55,941

      • MTD Local Currency (Aug-24) =  21,49,926

      • Common Currency YTD = 19,55,941+ 21,49,926 =  54,79,357

2. For Balance Accounts (Assets, Liabilities): Balance accounts represent a snapshot at a point in time (e.g., assets, liabilities).

• YTD Setting (Balance Accounts):

  • YTD = Current Period Balance

  • The YTD value is always equal to the balance in the current period, as balance accounts show a point-in-time value.

• MTD Setting (Balance Accounts):

  • MTD = Current Period Balance - Prior Period Balance

  • MTD is calculated as the difference between the current period's balance and the prior period's balance.  

    Let’s see an example for MTD and YTD:

    	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
    Exchange Rates	0.565	0.6723	0.4613	0.2576	0.5147	0.5571
    Local Currency MTD	-	-	-	-	-	-
    Local Currency YTD	56,00,566	56,00,566	56,00,566	56,00,566	56,00,566	56,00,566
    Common Currency MTD	-	6,00,940.73	-11,81,719.43	-11,40,835.29	14,39,905.52	2,37,464.00
    Common Currency YTD	31,64,319.79	37,65,260.52	25,83,541.10	14,42,705.80	28,82,611.32	31,20,075.32

    
    Calculating Common Currency YTD (Balance):

    Multiply each month’s YTD local currency amount by the exchange rate for that month.

    • For Jul-24:

      • YTD local currency = 56,00,566

      • Exchange rate = 0.565

      • Common Currency YTD = 56,00,566 * 0.565 = 31,64,319.79

    
    Calculating Common Currency MTD (Balance):

    Subtract the previous month’s YTD from the current month’s YTD.

    • For Aug-24:

      • YTD Common Currency (Aug-24) = 37,65,260.52

      • YTD Common Currency (Jul-24) = 31,64,319.79

      • Common Currency MTD = 37,65,260.52 - 31,64,319.79 = 6,00,940.73