I'm trying to build a payment schedule that shows the monthly payment on each month, but I'm having trouble with the conditions for populating the amount.
The table below shows what I'm trying to do - the values are shown in row two and the formulas in row three. The formula for total months works fine (unless someone has a better suggestion). It is intended to treat any partial month as a full month.
My problem is with the last two columns. How can I calculate, for example: IF(October is between Start and End Dates , Monthly Cost , 0). I anticipate each monthly payment column having the formula updated with that month: IF(Nov is between Start and End Date....etc.)
Unfortunately, I can't use VBA for this one, so I'm looking for formulas only. Thanks!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Cost[/TD]
[TD="align: center"]Total Months[/TD]
[TD]Monthly Cost[/TD]
[TD]October 2017 Payment[/TD]
[TD]November 2017 Payment[/TD]
[TD]December 2017 Payment[/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]3/15/18[/TD]
[TD]$500[/TD]
[TD="align: center"]5[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1[/TD]
[TD]=C1/D1[/TD]
[TD]???[/TD]
[TD]???[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]
The table below shows what I'm trying to do - the values are shown in row two and the formulas in row three. The formula for total months works fine (unless someone has a better suggestion). It is intended to treat any partial month as a full month.
My problem is with the last two columns. How can I calculate, for example: IF(October is between Start and End Dates , Monthly Cost , 0). I anticipate each monthly payment column having the formula updated with that month: IF(Nov is between Start and End Date....etc.)
Unfortunately, I can't use VBA for this one, so I'm looking for formulas only. Thanks!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Cost[/TD]
[TD="align: center"]Total Months[/TD]
[TD]Monthly Cost[/TD]
[TD]October 2017 Payment[/TD]
[TD]November 2017 Payment[/TD]
[TD]December 2017 Payment[/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]3/15/18[/TD]
[TD]$500[/TD]
[TD="align: center"]5[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1[/TD]
[TD]=C1/D1[/TD]
[TD]???[/TD]
[TD]???[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]