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

1

[Example] [1] [1]: link

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

13 - the problem is here; the clients that opt for monthly withdrawals will have the same value every month, exp R $ 10000,00 to 8% = R $ 800.00 every month, but I must dilute this amount for each day of the month, and return the income of the first day until the present day, and when it turns the month it all starts again.

    
asked by anonymous 06.06.2018 / 21:38

1 answer

0

Edit

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

Explanation

If the F column is different from the "No" string, divide the value of F2 by the number of days in the month and multiply by the current day. Otherwise, type empty ""

Original Response

See this answer in which shows how to check the number of days in each month of a year

To transform the same days VBA formula into 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)

So the following formula will go into the daily yield cell:

=10000/DIA(DATA(ANO(HOJE());MÊS(HOJE()+1);0)) , where 10000 can be the cell containing this value (eg $ G5)

    
06.06.2018 / 22:33