Prorata Calculation Using Dates

LeiCuntapay
LeiCuntapay
in Modeling

Almost done with my module. I need help in developing a formula to compute the monthly amortization that looks at dates.

image.png

Best Answer

  • SriNitya
    SriNitya
    edited February 22 Answer ✓

    @LeiCuntapay
    create boolean line items for start period, end period and middle periods and populate the booleans. By using the boolean line items you calculate as shown below
    start period line item→ if start boolean then Amount spread* Active days/ No.of days
    middle period line item → if middle boolean then Amount spread else 0
    start + Middle line item → start period+ middle period ( start & middle period data in single line item, keep summary method SUM)
    end period line item → if end boolean then amount- TIMESUM(start+ middle) else start + Middle
    so end period line item gives you the monthly amortization.
    By using individual lineitems or by merging the logics in single line item you can do this

    Hope this helps.
    Sri Nitya

Answers

  • deborah621
    deborah621

    This could be the total cost, salary, or any other amount that needs to be prorated. This is the number of days for which the pro rata calculation is required. Use 365 for a common year or 366 for a leap year. Multiply the total amount by the number of days in the period, then divide by the total days in the year.

Categories