• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Which formula should be used to calculate monthly interest, emi, closing balance, and annual year monthly in the table?

First and foremost, are you using Excel 365? If not, I suggest you change; with the type of tasks you are undertaking, you should be able to recover the cost of a license every week in saved time.
1728552297944.png
The formulas I used for the tables were
Code:
= REPORTbyFYλ(interest, date)

= REPORTbyFYλ(principal, date, MAX)

= REPORTbyFYλ(closingBal, date, MIN)

That tells you very little. The Lambda function I wrote to return the pivoted data is
Code:
REPORTbyFYλ

=LET(
    fn, IF(ISOMITTED(function), SUM, function),
    FY, FINANCIALYEARλ(date),
    FM, FINANCIALMONTHλ(date),
    PIVOTBY(FM, FY, quantity, fn, , , , 0)
)

That, in turn, contains functions that switch dates to financial years, starting April
Code:
FINANCIALYEARλ

=LET(
    adjustedDate, 1 + EOMONTH(+date, -4),
    FY,           YEAR(adjustedDate),
    CONCATENATE("FY", FY, "-", FY + 1)
)
Code:
FINANCIALMONTHλ

= LET(
    adjustedDate, 1 + EOMONTH(+date, -4),
    FM,           MONTH(adjustedDate),
    "FM" & TEXT(FM, "00 ") & TEXT(date, "mmm")
  )

You may observe that this bears little resemblance to traditional spreadsheet working. That observation would be correct.
Alongside the traditional methods of Excel, a completely different functionality has arisen that bears little similarity to the old.
 

Attachments

  • ReportByFY.xlsx
    34.2 KB · Views: 9
Last edited:
Your words are correct
But it has to work in all computers, so it is not possible to spend so much. But 365 is available for one computer and not for other computers.
Then there is a little problem.

But the formula you wrote works very well. Thank you for your intelligence, skill, patience and advice
 
Back
Top