# portfolio

1. ### calculating FV of an investment

Hi all, quick question. I am looking to calculate the future value (FV) of an investment. I have a lump sum to start with, a known rate of return and I would be making consistent, monthly contributions (\$100). However, I would also like to add a large payment once a year into the investment...
2. ### Combine two Vlookups in one cell

I need to locate two values and combine them in the same cell and add a text value. The final result would look like this: \$3000/\$6000. =VLOOKUP(\$D\$6,'Portfolio 2019.csv'!\$D\$4:\$AE\$116,5, FALSE) & =VLOOKUP(\$D\$6,'Portfolio 2019.csv'!\$D\$4:\$AE\$116,6, FALSE) I have tried a few different...
3. ### Converting AGGREGATE formula to VBA code

I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help. My formula: INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio...
4. ### Optimising portfolio with constraints

Hi guys, Hoping you can help me with this problem. I'm looking at some loan data. There are some concentration limits (3 examples below) in place for the loan portfolio: 1: Sum of outstanding loans larger than 400k, cannot be greater than 5% of the total portfolio 2: Sum of outstanding loans...
5. ### User Defined Function for stock variance

Hello, I need help in creating a custom function that will calculate variance of a stock using its raw prices. This means that the returns calculation will take place within the function. I have written the code, but its not giving the right answer, could anyone please guide me? Thanks in...
6. ### Minimum acceptable value with solver

Hello! I'm using solver for stock portfolio optimization. The solver changes weights to achieve maximum expected return. I have set the following constraints: The portfolio should consist of at least 5 stocks (out of 10) and maximum weight assigned for one stock is 30%. In this case the solver...
7. ### Dynamic Excel Sumifs/Countifs/Averageifs Formulas

I am looking to build a dynamic set of formulas that summarize Monthly and YTD information without having to update the formulas. For example, say I own a portfolio with several stores in the retail, food and services respectively industries and have a database that breakout daily sales for each...
8. ### Check and Capture first date not in order

Hello, Have a column representing dates in order of months a security is in a portfolio. Some months the security is not in the portfolio. Would like a formula to capture the first month the security comes back in the portfolio after a break. Eg listed below the security is absent between Jan...
9. ### Countif and IF Formula

I am using the following formula =if(countif('Copy of Portfolio Transactions'!\$A:\$A,"Avondale Tax*"),'Copy of Portfolio Transactions'!F3,"") The F3 Row is a dynamic field not static and every time I move the data around I do not get the results. Is there a way I can make it dynamic?
10. ### Portfolio Rollup VBA

I'd like to create a portfolio of "Projects" by duplicating the cashflows from a single Project based on a velocity and schedule of basic assumptions (number of projects per month during a range of months or years). I uploaded an illustration of the desired output to dropbox here. Thanks in...
11. ### How to dynamically order unique dates chronologically found in two tables?

Good afternoon board! I am constructing a portfolio and I want to auto generate my positions. My goal is to determine my portfolio position at every trade date. Context: I have two tables in a "Transaction History" worksheet, one with all the Trades and the other with all Dividends Received...
12. ### Extracting a substring from an array, eliminating duplicates

Hi everyone, I'm trying to set something up here and I'm a little lost in my functions, hopefully someone can help out! I have a dynamic array of a portfolio which has a series of symbols ex. OMCL-US (because it pulls data from a database that requires the "-US" portion). But now, I'm trying...
13. ### Count if and statements

I would like to be able to count based on these two conditions... I have a portfolio project named "Kim" and there are multiple subprojects under this portfolio with a priority assigned (numerical values used 1, 2, 3...etc) so I want to count how many projects are prioritized as a 1 for "Kim"...
14. ### Automatic portfolio maker

Hi, At the moment I'm writing on my thesis which is about Return and Reversal. To do this I need to construct a lot of portfolio's. At the moment I have 1100 stocks which I need to evaluate. As you can see from column A to C I have the different companies who where in the S&P 500 and from when...
15. ### Percentage of hours, based on size (weight?)

I have a table of portfolio's that have varying number of clients and work hours. Each portfolio has 1-10 clients depending on the amount of work hours that are assigned, ie a portfolio with a large client with lots of work hours will only have 1 client, and a portfolio with clients with a...
16. ### 2 text IF arguments

Hi How do I create to IF arguments for the same cell relating to text: So if cell A1 = "Non Portfolio" or "Portfolio status not yet known" then I want it to output "ALL COSTS", if false, then just blank. So normally for 1 argument it would be =IF(\$C\$33="Non Portfolio"," ","ALL COSTS") Thank...
17. ### using vlookup in the range of a sumif formula

I need to create a report that looks up data provided on another worksheet and spits out the sum of all trades maturing in less than or equal to 12 months. So the data looks like this: Worksheet A <tbody> Portfolio Maturing < 1yr ABC123 DEF345 GHI678 </tbody> Worksheet B <tbody>...
18. ### Monthly contract prices per portfolio

Hello, I have following system for updating contracts and their prices: <tbody> Portfolio Service Start End Monthly cost (Period 1: 1.4.2016-30.3.2017) Monthly cost (Period 1: 1.4.2017-30.3.2018) Portfolio 1 Service 1 1.5.2016 100  200  Portfolio 3 Service 2 1.7.2016 1.8.2017 300  200...
19. ### Live autoupdating line graph mirroting stock portfolio performance

Hi all, I'm not much of an Excel guru but I'm trying to build a replica of Google Finance Portfolios now that the Portfolios portion is being axed. I've managed to replicate everything into a live spreadsheet but the line graph. I would like to build a line graph that plots the value of my...
20. ### Index Match

Ok I have an index(Match formula I'm using.... IFERROR(INDEX('Master Portfolio Sheet'!C\$3:C\$187,MATCH(1,INDEX(('Economic Macro'!\$G\$2='Master Portfolio Sheet'!B\$3:B\$187)*('Economic Macro'!\$A18='Master Portfolio Sheet'!A\$3:A\$187),0),0)),"") I understand that this is making sure two variables are...