Hello All,
I've been struggling a lot with what seems to be a quite simple calculation. I am trying to make an Amortization Schedule for a Mortgage with variable interest rate. And using standard cell by cell formulas it can be easily done. (See attachment as example).
So I basically first start by calculating my monthly payment using PMT and the initial loan value. Then I can calculate the interest and the installment and subtract that from the initial loan to get the principal balance of the current month. Then I can just refer to that value in the row below and drag the formula all the way down. However, If I try to do exactly the same using Dynamic Ranges/formulas, it sees it as a circular reference simply since within the array there is the previous value. Is there any workaround for this? I need this to be a dynamic range. Also attached below.
I managed to use everything dynamic:
=-PMT(EIR_Mortgage_Monthly/12;Total_Payments_Mortgage-Month+1;P2:P25)
P2:P25 should be the Array of the end balance but thats where the Dynamic reference falls apart.
End Balance formula: Effective_Principal_Mortgage-SCAN(0;Installments_Monthly;LAMBDA(a;b;a+b))
Any help would be appreciated. Thanks
I've been struggling a lot with what seems to be a quite simple calculation. I am trying to make an Amortization Schedule for a Mortgage with variable interest rate. And using standard cell by cell formulas it can be easily done. (See attachment as example).
So I basically first start by calculating my monthly payment using PMT and the initial loan value. Then I can calculate the interest and the installment and subtract that from the initial loan to get the principal balance of the current month. Then I can just refer to that value in the row below and drag the formula all the way down. However, If I try to do exactly the same using Dynamic Ranges/formulas, it sees it as a circular reference simply since within the array there is the previous value. Is there any workaround for this? I need this to be a dynamic range. Also attached below.
I managed to use everything dynamic:
=-PMT(EIR_Mortgage_Monthly/12;Total_Payments_Mortgage-Month+1;P2:P25)
P2:P25 should be the Array of the end balance but thats where the Dynamic reference falls apart.
End Balance formula: Effective_Principal_Mortgage-SCAN(0;Installments_Monthly;LAMBDA(a;b;a+b))
Any help would be appreciated. Thanks