Show diluted value according to the last day of the month! Excel

Question

[Example][1] [1]: http://prntscr.com/jsd694

1 - investment by the client;
2 - commencement of contract;
3 - duration of contract;
4 - rate of return for each customer;
5 - whether the customer has chosen to withdraw the income monthly or not;
6 - amount to be deposited monthly for those who opted for monthly deposit;
7 - End date of contract;
8 - final amount to be withdrawn at contract end;
9 - administrative fee charged for each withdrawal;
10 - how many days have elapsed since the date of the contract;(=($A$1-C3))
11 - conversion of item 10 to months;(was the only way I found to have item 12 result)(=K3/(365/12))
12 - give me how much you have earned since the beginning of your contract; (=B3*(((1+E3) L3)-1))

13 - the problem is here; customers q opt for monthly withdrawals will have the same amount every month, Exp R $ 10000,00 to 8% = R $ 800,00 every month, but need to dilute this value for each day of the month, and return me the income from day one until the current day, and when the month comes around, it all starts all over again.

Answer 1

Edit

=SE($F2<>"Não";($F2/(DIA(DATA(ANO(HOJE());MÊS(HOJE())+1;0))))*DIA(HOJE());"")

Explanation

If column F is different from string "No", divide the value of F2 by the number of days of the month and multiply by today. If not, write empty ""

Original Response

Behold this answer where it shows how to check the amount of days in each month of a year

To transform the same VBA formula of days in the month DiasNoMes = Day(DateSerial(Year(Date), Month(Date)+1, 0))

For Excel formula, this function can be used: =DIA(DATA(ANO(HOJE());MÊS(HOJE())+1;0)) or =DIA(DATA(ANO(HOJE());MÊS(HOJE())+1;1)-1)

Therefore, the following formula will go in the daily income cell:

=10000/DIA(DATA(ANO(HOJE());MÊS(HOJE()+1);0)), where 10000 may be the cell containing this value (e.g.: $G5)