Oct 21 2022 01:28 AM
Hi I am looking for help with a formula to calculate outstanding portfolio.
Each investment has a maturity date a cash flow on the maturity. I want to calculate outstanding portfolio on each date in various category (<1 year, 2-3 years, etc).
I am trying this: =SUMIF(dateif(I$7,$E$6:$E$98,"y"),"<1",$C$6:$C$98)
Also I am not able to add screen shot here for some reason.
Thanks in advance,
Oct 21 2022 08:54 AM
Sorry, I was trying to upload the screen shot but can't seem to be able to do that.
I7 - reference date for calculating outstanding amounts for various maturity
E6:E98 - Maturity dates
C6:C98 - Is principal amount that will be repaid on maturity.
I want to calculate amount that will mature in in x years from I7.
Thanks
AB
Oct 21 2022 10:00 AM
Let's say "x" is in A10
=SUM((DATEDIF(I$7,$E$6:$E$98,"y")<A10)*$C$6:$C$98)
If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
The formula can be filled down or to the right if you have other values of "x" below or to the right of A10.
Nov 15 2022 03:04 PM
Nov 15 2022 03:26 PM
I assume that cell A56 contains a number. If so:
=SUMIFS(CF[Total
CF],CF[Days to
Payment],"<"&A56,CF[Type],"=Dividend",CF[Type],"=Interest")