Calculating average interest rate for a month

[Lanthanide]

I've taken the default Excel loan amortization and made some changes, to use it to model my mortgage. Specifically in New Zealand, we have a lot of interest rate changes in our mortgages (not like the US where they are typically fixed-rate for the duration).

The interest rate changes usually don't line up neatly with the repayment period for the loan. Eg the next repayment might be on the 20th of the month, and the interest rate was previously 6%, but on the 10th it reduces to 5.5%. The interest is calculated daily, so in this case you'd need ~20 days of interest at 6% and 10 days at 5.5%, or an average monthly interest rate of 5.83%.

I've done some googling and solved two problems to setting up the spreadsheet to calculate the average monthly interest rate easily, but I can't find a way to do the final step, which requires getting an array of minimum values, eg:
Given column A with values in cells A1, A2, A3, A4, I need to MIN operation for each of these cells against another single cell, B1, eg:
MIN(A1,B1), MIN(A2,B1), MIN(A3,B1), MIN(A4,B1). I suspect this is ends up being an 'array' formula but I'm not really familiar with these.

Anyway, here is a google sheets example, which should be editable, highlighting the result I want to calculate (hopefully with the above explanation this will all make sense).

https://docs.google.com/spreadsheets/d/10PW16teIDVOLJiyfj8KQxxYyOUBvcpoberu2InetWpM/edit?usp=sharing

The solutions I googled that I'm using in this sheet are the following:
http://chandoo.org/wp/2010/06/30/range-lookup-excel/
http://chandoo.org/wp/2010/07/07/days-overlapped-excel-formula/

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Lanthanide]

Well, it works! But it's pretty clumsy.

The mortgage is for 30 years (plan to pay it off in 15 or less, though!) and there are going to be many more interest rate adjustments in the future. So while your solution works, it is pretty 'hard-coded' for the existing interest rate periods, and when new periods are added in the future the formula is going to get very long and ugly, fast.

What I liked about the example PRODUCTSUM solution is that adding a new interest rate period only requires the ranges to be extended to include the new row and that's it.

 

[NARAYANK991]

Hi ,

I find it difficult to digest your choice of words , and difficult to understand your attitude.

Please upload a sample workbook to illustrate all the complexity of your application , and I am sure someone else will suggest a less clumsy / ugly solution.

Narayan

 

[Lanthanide]

I had another look at your formula and actually I misunderstood it to start with. I thought each block of IF($B$3:$B$5<$A9+1;$B$3:$B$5;$A9+1) etc represented 1 row of the interest rate periods, and so each extra row would need another if statement. But it doesn't work that way; only the ranges would need to expand, as I mentioned about the PRODUCTSUM formula.

Anyway I extended the formula out to simulate the next few loan payments. While your formula comes up with the correct result 5.10% for the payment due on 20/9/2015, it doesn't work for the next 3 payments due in October, November and December - it gives values of 4.55% or 4.54%, but the correct value is 4.40%.

New spreadsheet with your formula extended to the extra dates, attached.

 

[Lanthanide]

Right, I've been playing with this for a while now.

I see there is no other elegant way to replace the negative numbers with 0s, other than using the "if (x>0; x; 0)" construction, which is unforunate since it makes the formula quite long and cumbersome to update. It seems Excel doesn't have a nice null coalescing operator / elvis operator so there is no alternative to that.

I found and fixed the problem with the 4.55%, it was the extra "+1" being added onto the value of A9. I played with it for a while to try and resolve it but there were side-effects on whatever I did. Ultimately I had to change the data, so that instead of 31/7 to 15/9, and 16/9/2015 to 15/9/2017, it is now 31/7 to 15/9, and then 15/9/2015 to 15/9/2017. This fixes it without adding side-effects.

Thanks very much for your help NARAYANK991!