Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet? Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.
Download the mutual fund tracker – India now.
NOTE: File updated on 7 July 2018 to fix errors. Download again if you need to.
How to use this mutual fund tracker Excel workbook?
- Download and save the file to a folder on your computer (do not leave it in the downloads folder)
- Open the file in Excel (you need Excel 2016 / Office 365 to use this file. If you are using older version of Excel, you need free Power Query add-in)
- If prompted, enable “External connections”
- Go to Data and click on Refresh all
- This will fetch updated funds list and latest NAV (Net Asset Value) from AMFI India website.
- On the My Funds page, specify the funds you own, units, purchase price and purchase date
- The tracker will calculate your return, CAGR (Compounded annual growth rate of funds) and simple return of your portfolio and display it
- When you want to see updated value of your portfolio, simply refresh the tracker (Data > Refresh All or press CTRL+ALT+F5)
How this mutual fund tracker is made?
We will use 3 simple excel features to achieve this – Power Query, Cascading Drop downs and vlookup()
- First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.When you finish creating the table, it would look something like this:
- Next we will use Power Query to automatically fetch the funds and latest fund value from AMFI website. This site maintains updated funds and values and publishes the list as TXT file at URL- https://www.amfiindia.com/spages/NAVAll.txt We can connect to this page as web URL from PQ and setup some simple rules to clean the data and extract the relevant bits we need. The result looks like this in Power Query editor.
- Once we load this data to Excel, we can build a simple 2 level cascading drop-down system to capture details on the “my funds” page. That way, our drop down will be small and easy to use. Please read cascading drop downs page for details on how to do this.
- Finally, based on the fund name, we fetch the NAV and NAV date using, you guessed it – VLOOKUP formula. The rest is easy to calculate.
- Now as you input fund names and refresh data, your portfolio gets updated.
Few ideas on how you can enhance this:
- Add graphs to see visually how the funds are doing
- Build some VBA to store previous NAV values of your funds so that you can see historical dates
Problems with MF Tracker?
- I cannot refresh data: You need internet connection. You also need Power Query for Excel (this is available by default in Excel 2016 or above, Office 365. If you have an older version of Excel, download Power Query add-in for free.
- I see some error during download / refresh: The AMFI people have been inconsistent with their file formatting. It could be a weekly issue (ie every Friday they might be publishing some extra data.) Try again in a day or two. If the problem persists, post a comment so I can suggest a work around.
- Some other problem: Please post a comment so I can look in to this for you.
15 Responses to “Modeling Interest During Construction (IDC) – Excel Project Finance”
Thanks again for a very helpful post.
I had a similar problem when trying to model a balance sheet and profit and loss projection. The problem was that interest expense (in P&L) was dependent on a cash shortfall (in BS) which had to be funded. The cash shortfall depended on how much interest was paid, so the mutual dependency made a circular reference.
I addressed it with a macro that calculated interest outside of the P&L, then pasted the calculated amount into the P&L as a value. The model was out of balance, but by repeating the pasting and calculating loop the imbalance reduced to zero. It was a bit messy, and had to be repeated every time a line changed - but it worked.
If I have to do it again I'll read this article again first and see if it can be done more elegantly.
Hi,
The use of a circular reference can be avoided in this case. Just make use of the geometric sum to calculate the interest required. I’ll walk through the example from the spreadsheet.
First calculate the cash needed each year without the interest expense. So you year 1 you need 55 Mn, year 2 105 Mn, and 190 Mn for year 3. The total amount to borrow for year 1 is then (50 Mn)/(1-interest_rate) = (50)/(1-0.1). For years 2 and subsequent the amount borrowed is the cash needed in that year plus the interest_rate times the amount already borrowed. For year 2 (105 + interest_rate * sum(previous debt raised))/(1-interest_rate)=(105+0.1*61.1)/(1-0.1).
This process avoids the need for a circular reference, and makes the calculation more stable.
Thanks,
Tristan
The question is for the year 1 in your case, the amount works out to 45 mn. However in the year 2 you have applied the loan amount as 61.1 mn.
Am I missing something ! Please help !
very helpful information!!!
using circular references and to make model more stable we can use combination of "IF" and "ISERROR" functions. i.e
=if(iserror(formula1),"",(formula1))
this formula will return blank value if there is any error otherwise give the result required.
I usually use this in my models and it makes them very stable......
🙂 🙂 🙂
@Terry: Thats right. Exactly same problem is seen in Interest - Cash cycle in P&L and Cash Flow statement as well. In our trainings on financial modeling in excel, we demonstrate using both the circular loops as well as the macros to take care of this problem. Circular loops have their own pitfalls. If the model enters into a state of error, the error percolates!
@Tristan: Thanks for pointing out. I agree with you that if circular loops can be avoided, they should be avoided.
@Yogesh: This is one way of avoiding the problem. Although circular loops have another problem that they make your sheet slower. Each time, there is a change in the sheet, all the calculations are redone. So if they can be avoided, they should be avoided.
Please note that this was an example (a large one indeed) and I didn't have space to speak about the pitfalls of this approach! I just wanted to illustrate an approach and am glad that some of you found it useful!
I think while posting, there is an error in the images! The last image should be flipped with the one that is posted in step VII!
I think you can try the following simple solution given by Microsoft itself to make the circular works:
Windows: Excel Options -> Formulas -> Put a tick on "Enable iterative calculation"
Mac: Excel -> Preference -> Calculation ->Put a tick on "Limit iteration"
You can change the maximum number of calculation iterations as well as the maximum changes which iteration stops for goal seeking or for resolving circular references based on the number you type in the maximum change box.
Thank you.
Hey All
I heard that we can take care of the circularity with the help of macro for IDC. Can anybody help on the steps to construct the macro for the same.
Regards
Vinay
Hi Vinay,
If you look closely, you are essentially copying the values from the interest calculation to the IDC in project cost.
Basically you can record a macro, that takes the values from interest and pastes special the values in IDC row in project cost.
Then you can run that recorded code in a for loop.
Hope this helps.
Thanks Param for reply.
But before calculating interest, i need to provide for Upfront Equity and Equity, which are essentially part of total project cost. Hence, i need to put in Upfront Equity and Equity to calculate the IDC which is again hitting the total project cost.
Bit of confused on how to remove this circular reference.
Regards
Vinay
Wow, this was a brilliantly simple post. I was looking online for a while before I found this page. Never seen this been explained so beautifully yet so crisply before. Thanks for saving my ass at work! (i'm relatively new to finance + modeling)
I'm not sure why but this web site is loading very slow for
me. Is anyone else having this issue or is it a problem on my end?
I'll check back later and see if the problem still exists.
[…] Project Finance Modeling using Excel – Part 1 & Part 2 […]
I have been reading your blog since my college days. Today, I'm writing just to say thanks.
We have calculated Financial Rate of return of a hydropower projects, and the observer has raised an observation regarding Total Project cost with IDC Rs. 8616.01 million (PKR) and with-out IDC 8352.46 million (PKR). How does the Financial nalysis be calculated on the basis of with-out IDC Or With IDC?????
Please helpf. if possible to spare some time.