Interest calculator for unequal monthly installment, sample attached

Hello Everybody,

I'm trying to calculate interest on each monthly installment at the end of every month using a mega formula. Please see the attached sample workbook.

I've few questions:

1. The mega formula I used looks hell complicated, I'm quite sure there has to be a simpler solution.
2. How to convert this mega formula and principal sum formula into array formulae.

Thanks


Sample Interest Calculator.xls

Guys please help me on this.

I'm unable to form an array formula also as requested in Point 2.

Can you post the formula for one of the months here in the thread

And why do you need an array

@dredwolf: I will try that once I am at my PC. Thanks

^^ Thanks, I will try and update here.

You have 12 monthly payments, and as you can see you are needing a 12x12 matrix to do your calculations not to mention the MEGA long formula as stated in your own words

If you were to be restricted to using Excel native functionality then you are stuck with using such lengthy calculations of interest for each month and then accumulation of such interest payments to find the sub-totals

None of this is required if you use this Excel NFV function to find the accumulated amount for each month's interest+principal and finally removing the principal from this amount to get the monthly interest amount

As of now you have more than 15 columns in your workbook, using tadNFV function, you will require only 3 columns in your workbook

The function will take cash flows for up to each month and will report the compound amount that includes the principal and the interest due on each amount

You will lessen the up to month principal from the fore mentioned amount to get your Monthly Installment

Say you have your monthly payments in Column B and rows 5 to 16 for each of the 12 months

-----	A	B	C	D
1
2	Rate of	Interest	12%
3
4		MI	Total Interest	Total Principal
5		2500	=tadNFV($C$2/12, $B$5:B5, 0) - D5	=SUM($B$5:B5)
6		5000	=tadNFV($C$2/12, $B$5:B6, 0) - D6	=SUM($B$5:B6)
15		2800	=tadNFV($C$2/12, $B$5:B15, 0) - D15	=SUM($B$5:B15)
16		3700	=tadNFV($C$2/12, $B$5:B16, 0) - D16	=SUM($B$5:B16)

I have skipped the rows 7 through 14 from the table to lessen the clutter it might cause had I shown you all rows. But you get the idea

Cells C5 through C16 will have your total interest for each of the months

Also see the attached worksheet to view the calculations

Just to add a bit more to last reply

Calculations in your original file are rounding off numbers thus those interest amounts in your worksheet for month 4 and onwards are incorrect due to rounding off

Thanks, that worked absolutely great.

However I couldn't understand the use of MATCH function in the formula, use of functions ROWS and MAX also made it complicated for me to understand.

I simplified the formula for my understanding.

Sample Interest Calculator.sol3.xls

Thanks for the help.