I am attaching an excel file which explains my problem. IPMT returns interest payments for any period within a set of periods given rate of interest, present value, period and number of periods provided payments are constant over the period and I won't have to chart a amortisation chart for finding interest payments.
Problem arises for uneven cash flows. IPMT does not work in this case. Inputs are highlighted in yellow. Also I added a amortisation table for a check. As can be seen when cash flows are even 1000 over 5 periods, this formula does the job. But when I vary this cash flow as given in scenario 2 interest payments don't flow. Please note I tackled finding present value. What is left is the interest part.
Is there a function or workaround formula that fulfills my requirement without having to resort to a amortisation table?
Bookmarks