Formula issue - mortgage/installment plan

Camel123

Board Regular
Joined
Jun 5, 2018
Messages
186
Hi,

Please see the picture linked to below and open it in a separate tab to zoom it:
https://imgur.com/a/8v1i12O

I am trying to create a formula to use in O2:Z2 (blue cells). I want it to do the following:

Look for invoices in C3:N3, if true, then divide it by number of mortgage months in B3, and plan this amount for the same number of months as in B3 - starting the month after receiving the invoice. It should stack with mortgages related to invoices in other periods.

As in the picture, we get the first invoice= 1200$ in 2020/01, the second invoice= 1200$ in 2020/06,
mortgage of the first invoice start by 2020/02 with 1200/12= 100$ a month during 12 months according to the number of mortgage months,
mortgage of the second invoice start by 2020/07 with 1200/12= 100$ a month during 12 months according to the number of mortgage months,
from 2020/07 since mortgages are overlappning, the mortgage payments stack and correspond 100+100= 200$ a month..

I tried to get this working but can't, would really appreciate your help here.
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Also tried to attach the workbook but could not find the function (?) Would really appreciate your help on this issue need to have it done before Monday, still haven't managed to solve it.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top