Depreciation Schedule • IRR Calculator • EMI Calculator

1. Depreciation Schedule Automation Tool

A. Problems - Before the Tool (Manual Preparation in Microsoft Excel – Difficulties and Limitations)

1. Earlier depreciation schedules were prepared manually using formulas in Microsoft Excel.

2. Every asset required separate calculation of:

o Opening value

o Additions

o Applicable depreciation rate

o Depreciation amount

o Closing value

3. Preparing depreciation as per Companies Act or AS 10 (PPE) and as per Income-Tax Act together was extremely difficult because both laws follow different rules, different logics, and different treatment of assets.

4. Identifying the correct FY based on purchase and sale dates required manual checking.

5. For calculating depreciation as per Income Tax Act, 1961 Determining whether an asset falls under :

o Addition before 04 October 2022

o Addition after 04 October 2022 was time-consuming and had chances of error.

6. For every subsequent year calculation, copying formulas across rows often resulted in incorrect references and miscalculations.

7. Any Changes in categories or rates disturbed formulas throughout the sheet.

8. Manual formatting (headings, borders, totals) took extra time every year.

9. With many assets, the time required increased drastically.

10. The overall manual process consumed too much time and required repeated checking.

11. Calculating deferred tax Asset/ liability based on WDV as per books of accounts and as per Income tax act, 1961, also used to be worked out through based on manual entering formula and time consuming

B. Why the Idea Was Required

1. A consistent and error-proof method without manual intervention was needed for every client.

2. A system was needed that applies correct rules for both Acts automatically.

3. Auto calculation for every subsequent year was needed with auto calculation of deffered Tax asset & liability

C. After Creating the Depreciation Tool using VBA (Advantages)

1. Full depreciation schedule is generated instantly in one click.

2. The tool automatically:

o Reads all asset details

o Identifies earliest FY

o Applies logic for Income tax Act and Companies Act, separately

o Fetches correct depreciation rates

o Prepares depreciation as per both laws separately

3. Eliminates formula mistakes completely.

4. Generates Deferred Tax Asset/ Liability sheet simultaneously on the same click in which depreciation schedule is prepared.

5. Output is professionally formatted every time.

6. Saves nearly 90% time.

7. Can be used easily by any unskilled staff member too.

2. IRR Calculator (VBA-Based User Form)

A. Problems - Before the Tool (Problems in Manual IRR Working)

1. IRR calculations required creating cash-flow tables manually in Microsoft Excel.

2. Maintaining monthly, quarterly, and yearly intervals manually was difficult and confusing.

3. IRR gave wrong results when cash-flow timing or structure was irregular.

4. Staff struggled with:

o Multiple outflows

o Multiple inflows

o Mixed inflow–outflow patterns

5. Preparing proper tables for each case wasted a lot of time and created scope for error.

B. Why the Idea Was Required

1. IRR is used in project reports, investment decisions, and bank presentations.

2. A simple tool was needed to calculate IRR quickly without creating long tables.

3. Staff required an easy method to compute correct IRR even in complex structures.

4. Accuracy was essential for professional reporting.

C. After Creating the IRR Tool using VBA (Advantages)

1. IRR is calculated instantly without any manual table preparation.

2. The tool automatically expands cash flows based on:

Monthly

o Quarterly

o Yearly

3. Supports all combinations:

o One outflow & one inflow

o One outflow & series of inflows

o Series of outflows & series of inflows

4. The IRR calculation is based on the same formula taught in the Chartered Accountancy course, ensuring 100% conceptual accuracy.

5. Results match professional IRR calculators.

6. Eliminates human errors and saves significant time.

---

3. EMI Calculator (Advanced VBA Tool)

A. Problems - Before the Tool (Limitations of Google EMI Tools and Manual Methods)

1. Google EMI calculators provide only EMI, total interest, and total payout — they do not provide month-wise details.

2. They cannot calculate:

o Opening balance

o Interest & principal for each month

o Closing balance

o Start-date-based schedules

o FY-wise summaries

3. Not suitable for:

o Income Tax interest calculations

o Voucher posting

o Loan account finalisation

4. Manual calculations in Excel required long formulas and repeated checking.

B. Why the Idea Was Required

1. CA work requires full EMI schedules for accounting, taxation, and project report taskss.

2. FY-wise breakup of interest and principal is essential for tax deductions and ledger posting.

3. A tool was needed to generate schedules instantly and accurately.

C. After Creating the EMI Tool using VBA (Advantages)

1. Produces a complete month-wise schedule showing:

o Opening balance

o EMI

o Interest

o Principal

o Closing balance

2. Provides total interest and total principal automatically.

3. Generates FY-wise summary of:

o Interest paid → for tax deduction

o Principal repaid → for accounting

4. Stronger than Google EMI tools because it also provides:

o Full amortisation schedule

o FY summary

o Pie chart

o Professional formatting

o Multiple scenario analysis

5. Removes old schedules automatically to maintain clean output.

6. Accurate, fast, and highly useful for professional CA work.

Conclusion

These tools were created to replace earlier slow, manual, and error-prone processes. With VBA automation, depreciation schedules, IRR calculations, and EMI schedules can now be generated instantly with complete accuracy and professional presentation.