Gold Prices in India

I was working on small project and looking at the data of Gold prices. Here's Google spreadsheet, if anyone wants to play with the data.

Interesting facts - Compounded annual growth rate of Gold from 1947 to 2019 is at 8.5%, that's the compounded return you got for staying invested for 73 years (I am using both 1947 & 2019 as inclusive). I also stumbled upon wonderful analysis done by Jago Investor while looking for data.

Absolute prices does lot of psychological damage to us ;)

How to calculate EMI in Excel

Continuing with my last post on finding out how much you will make using compound interest, trying to put down the simple method you can use to find out how much Equated Monthly Installment (aka EMI) you need to make for the loan you bought.

Erstwhile, it was predominately for Home loans but now with culture shift and new generation opting for easy loan features for buying the consumption goods (read as high end mobiles, cars etc) - its even more important to know the calculation and find out the real amount you might be paying up.

So let's quickly get into the Excel formula called PMT

=PMT(RATE,NPER,PV,FV,TYPE)

Here,
Rate stands for rate of interest applicable on the loan
NPER stands for total number of monthly installments/ loan tenure
PV stands for present value/ loan amount/ principal amount
FV stands for future value or cash balance once last payment has been made. This can be omitted and the value will be counted as zero (0).

Type is zero (numerical 0) or 1 – this indicates when the payment is due. If payment is due at the end of the period, the type will be equal to zero. If the payment is due at the commencement of the month, then the type will be set as 1.

Important to remember is, keep both Rate and NPER in same terms - which basically mean, if NPER is in month, ensure Rate is also monthly.

Let's take a quick example: You bought a Car worth Rs 7,00,000 by paying making a down payment of Rs 1,00,000 and took an auto loan for the rest of the amount at 12% interest per annum for four years. What would be your EMI amount which you need to pay each month?

Here: Rate is 12% per annum, NPER = 4 years, PV = 6,00,000 (this is loan amount)
I am converting both Rate and NPER to months in example below, as amount needs to be paid each month

Formula in Excel would read as below and answer should be Rs 15,800 which you need to pay each month for 4 years towards the car loan
=PMT(12%/12,4*12,600000,0,0)

Output would be negative, as that is the monthly outflow you are paying.

Once you get hang of it, it's pretty easy to calculate and find out the liability/debt you are entering in. Hope this helps you in making better financial decisions.

How to calculate Compound Interest

Everyone agrees magic of compounding and power of compounding. No one needs any explanation on this. So next question comes, how do you compute it and make decision on outcome you are looking at. Bear in mind, I am discussing here from the savings perspective, however, the same holds true for the loan as well.

Going to keep this very simple without going into smaller tiny nitty-gritty. I am assuming you have access to Microsoft Excel (you can use online free version by creating file in OneDrive as well). I am currently using Excel Office 365 version.

There’s an inbuilt function in Excel called FV, which I am going to use.

FV(rate,nper,pmt,[pv],[type])

The FV function syntax has the following arguments:

Rate - The interest rate per period.
Nper - The total number of payment periods in an annuity.
Pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv (Optional argument) - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
Type (Optional argument) - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
Set type equal to 0, if payment is due at the end of the period and 1 if payment is due at the beginning of the period.

Important – Ensure arguments for Rate and Nper are both on same basis, i.e. if ‘Rate’ is measured as month, ‘Nper’ should be also measured in month.

I am not getting into explaining the mathematical formula (you can refer to Investopedia for the same). Instead, I am going to take few examples and show you, how you would compute the same in Excel.

Example 1 – You make a term deposit of Rs 1,00,000 for 5 year at 6% interest rate with interest compounded annually. What would be the return?

Here, Rate = 6% (yearly), Nper = 5 (yearly), PV = Rs 1,00,000, I am assuming interest is credited at end of the period. So formula would be:

=-FV(6%,5,0,100000,0) ; if correctly done, you should see the output of Rs 1,33,823. Meaning you will be earning Rs 33,823 of total interest on compounded basis.

Note – I have taken negative sign at the beginning of the formula, as output would be otherwise negative to show what the future value basis given assumptions.

Example 2 – You are planning to make Recurring Deposit of Rs 5,000 each month for 12 months at interest of 6%, what would be the return?

Here, Rate = 6%/12 (converting yearly to monthly), Nper = 12 (number of months), PMT = Rs 5,000, I am assuming interest is credited at end of the period. So formula would be:

=-FV(6%/12,12,5000,0,1) ; if correctly done, you should see the output of Rs 61,678. Meaning you will be earning Rs 1,678 of total interest on compounded basis.

Here’s Quiz – how would your formula look like, if you have Rs 1,00,000 as initial investment and you are going to add Rs 5,000 each month for 12 months. Interest rate is 6% per annum compounded monthly. Consider interest credit at the end of the period.

Check in comments, if you want to see the solution.

Success Mantra for Financial Independence

This post is going to reveal the golden formula on how to achieve 100% success on your path to financial independence.

You would come across numerous Personal Finance posts on internet - all ultimately helping / directing you to one of this steps. So let's just jump in and start our journey...

Step 1 - Save.. Save.. Save... stop looking out for personal finance essays and posts. First make the target of achieving x% of your income as saving. Now this x% can be significantly different for each, depending on whether you are married or not, you have dependent parents or not and so on. So ignore the noise and start working to improve your % with each passing day.

Step 2 - Avoid debt. No point in looking at investment ideas, till you are servicing a debt. Pay off your debt first with the savings.

Step 3 - Enjoy power of compounding. Start investing your savings gradually and constantly. Do not break for short term needs. Let money grow over period of time and you will soon see the power of compounding returns.

Key goal to any personal finance management is keeping things simple.

Share me your approach and thoughts...