VanChester
01-31-2019, 04:01 PM
Hello everyone,
Using a free cash-flow template online, I have started to modify it to best fit the following scenario.
My goal is to create an excel sheet that will display all of the cash-flows from contract earnings.
there are 3 types of contracts:
1) "Fixed Monthly": Fixed monthly payments for the year.
2) "Yearly": Lump sum payment for the year.
3) "Special": for the first year, the first month required 25% of the total yearly amount, and can either complete the remainder (75% of the total yearly amount) in 4 or 5 equal payments the following months.
I have created an excel workbook that starts with an "Inputs" sheet where we can input the name/code of the client, payment type, payment start date, payment end date, and total yearly amount. I have then added 12 sheets that represent the 12 months of the year (To be able to upload the excel file here, I had to limit it to 3 months instead of 12, I imagine the process is the same for the remaining months). These 12 monthly sheets display the cash-flow of each client for every day of that month.I was able to create some basic formulas to display the the unique yearly payments into the appropriate monthly sheet.
where I am struggling is for the "Fixed Monthly" and the "Special" payment types.
for the "Fixed Monthly", I do not know how to make it so that excel displays the monthly payment not only on the first month it is applied, but the follow months too until the last payment date 23687. (I'm guessing VBA coding would be best and not basic IF-statement formulas). As for the "special" payment type, I am guessing it should vary just a little compared to the Fixed Monthly while adding multiplication factors into it.
Here is an example of the formula I have added on the Monthly Cash-flow chart of Janurary 1st:
=IFERROR(IF(AND(VALUE(LEFT(Inputs!$D4,2))=B$2,(VALUE(MID(Inputs!$D4,4,2))=' Monthly C.F. JAN'!$B$1)),IF(Inputs!$C4="Fixed Monthly",Inputs!$G4/12,IF(Inputs!$C4="Yearly",Inputs!$G4,"")),""),"")
One thing to note: For the date inputs, the only way I was able to make it work was for excel not to format it as a date. I therefore separated the day, month, and year with dots instead of forward slash. so the format is dd.mm.yyyy instead of dd/mm/yyyy. using this allows me to connect the day with the day numbered on each sheet, month with each month etc.
I have attached the excel document I have been working on so it is easier to follow.
Thank you so much for your assistance in this project of mine:)
Using a free cash-flow template online, I have started to modify it to best fit the following scenario.
My goal is to create an excel sheet that will display all of the cash-flows from contract earnings.
there are 3 types of contracts:
1) "Fixed Monthly": Fixed monthly payments for the year.
2) "Yearly": Lump sum payment for the year.
3) "Special": for the first year, the first month required 25% of the total yearly amount, and can either complete the remainder (75% of the total yearly amount) in 4 or 5 equal payments the following months.
I have created an excel workbook that starts with an "Inputs" sheet where we can input the name/code of the client, payment type, payment start date, payment end date, and total yearly amount. I have then added 12 sheets that represent the 12 months of the year (To be able to upload the excel file here, I had to limit it to 3 months instead of 12, I imagine the process is the same for the remaining months). These 12 monthly sheets display the cash-flow of each client for every day of that month.I was able to create some basic formulas to display the the unique yearly payments into the appropriate monthly sheet.
where I am struggling is for the "Fixed Monthly" and the "Special" payment types.
for the "Fixed Monthly", I do not know how to make it so that excel displays the monthly payment not only on the first month it is applied, but the follow months too until the last payment date 23687. (I'm guessing VBA coding would be best and not basic IF-statement formulas). As for the "special" payment type, I am guessing it should vary just a little compared to the Fixed Monthly while adding multiplication factors into it.
Here is an example of the formula I have added on the Monthly Cash-flow chart of Janurary 1st:
=IFERROR(IF(AND(VALUE(LEFT(Inputs!$D4,2))=B$2,(VALUE(MID(Inputs!$D4,4,2))=' Monthly C.F. JAN'!$B$1)),IF(Inputs!$C4="Fixed Monthly",Inputs!$G4/12,IF(Inputs!$C4="Yearly",Inputs!$G4,"")),""),"")
One thing to note: For the date inputs, the only way I was able to make it work was for excel not to format it as a date. I therefore separated the day, month, and year with dots instead of forward slash. so the format is dd.mm.yyyy instead of dd/mm/yyyy. using this allows me to connect the day with the day numbered on each sheet, month with each month etc.
I have attached the excel document I have been working on so it is easier to follow.
Thank you so much for your assistance in this project of mine:)