Do you hold a mutual fund portfolio, want to understand a snapshot in a easy to maintain way? If so, please read on.
- Create a spreadsheet on Google Spreadsheets. Name the spreadsheet as you need.
- Let us create a sheet and name the sheet as "NAV". This sheet will hold all the latest NAV and will be updated with latest price automatically. Let us visit this sheet later for automating the price update.
- Now, let us create a new sheet, call it "Portfolio".
- Create columns in the 1st row as follows:
- AMFI Scheme ID
- Account Number
- AMC
- Scheme Name
- Unit Balance
- Cost - Dividends
- Value
- Returns
- Return (%)
- AV Cost / Unit
- NAV
- NAV Date
- Let us shift the tab to "NAV". In the column A1 enter the formula as
- =IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt")
- This will update the NAV automatically and we will use this data to update information in our portfolio. This will have following columns now:
- Scheme Code
- ISIN Div Payout / ISIN Growth
- ISIN Div Reinvestment
- Scheme Name
- Net Asset Value
- Date
- Now find the scheme you have invested in from the NAV tab and get the Scheme Code and copy to AMFI Scheme ID column on "Portfolio" sheet. Similarly you can copy the Scheme Name.
- Get your statement from the AMC and update the Unit Balance, Cost - Dividends columns.
- In the "Portfolio" sheet enter the formula against NAV
- =INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,5)
- In the "Portfolio" sheet enter the formula against NAV Date
- =INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,6)
- Value column is the product of Units x NAV
- Returns is Value-(Cost-dividends)
- Return % is Returns / (Cost-dividends)
- AV Cost / Unit is (Cost-dividends) / Unit Balance
- Now create a summary line in the bottom for Cost-Dividends, Value, Returns and Return %
Voila! Your portfolio will be updated automatically when you open the spreadsheet. Just ensure you update unit balance and cost-dividends for accurate computations.
No comments:
Post a Comment