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!
<tbody>
</tbody>
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!
Start Date | End Date | Total Cost | Total Months | Monthly Cost | October 2017 Payment | November 2017 Payment | December 2017 Payment |
11/1/17 | 3/15/18 | $500 | 5 | 100 | 0 | 100 | 100 |
Manual Input | Manual Input | Manual Input | =(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1 | =C1/D1 | ??? | ??? | ??? |
<tbody>
</tbody>