How to Calculate Gold Loan Interest in Excel (2 Ways)

In this tutorial, I will show you how to calculate the amount of gold loan interest in Excel. Before applying for a loan, you can calculate periodic loan payments and interests to assess loan schemes provided by financial institutions. You can use the PMT function of MS Excel to find gold loan interest. Besides, you can use a mathematical formula to find loan interests. So, let’s have a look at the following discussion.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


What Does the Gold Loan Interest Mean?

A gold loan is a kind of secured loan that is provided by financial institutions (e.g. Banks) in exchange for the gold owned by the loan applicant. It is one of the quickest ways to borrow money from banks or any other financial institution. This is because disbursement of the loan amount is much quicker in the case of a gold loan. However, like other loans, you have to pay a certain amount of interest periodically against the gold loan. Usually, the loan interest depends on several factors such as:

➤ The amount of loan amount; the lesser the loan amount, the greater the interest rate.

➤ Purity of your gold and the current market price. The pure the gold is, the higher the loan amount will be.


2 Ways to Calculate Gold Loan Interest in Excel

1. Excel PMT Function to Calculate Gold Loan Interest

Prior to starting the calculation, for your better understanding, let’s have a look at the syntax of the PMT function:

PMT(rate, nper, pv, [fv], [type])

Here,

rate is the annual interest rate of the loan.

nper is the loan tenure (number of years for which the loan is approved).

pv is the principal value or the initial loan amount.

fv is the future value; optional.

type specifies when the payment is due; optional.

Now, suppose you have taken a 1-year gold loan from a bank with an interest rate of 2% and a loan amount of $5,000. Let’s calculate the monthly loan payment for the borrowed amount and then find the total interest incurred upon the loan.

Steps:

  • Type the below formula in Cell C9 and hit Enter.
=-PMT(C4/12,C5*12,C6)

Excel PMT Function to Calculate Gold Loan Interest

Here, in the PMT formula, I have divided the interest rate by 12 as I am calculating loan payments for each month.

  • As a result, we will get the monthly payment for the loan.

Excel PMT Function to Calculate Gold Loan Interest

  • Yet, we need to calculate the interest on the loan. To do this, first, we need to find out how much total money we will have to pay the bank in 1 year (12 months). So type the below formula in Cell C10 to get the total payable amount.
=C9*12

Excel PMT Function to Calculate Gold Loan Interest

  • Press Enter.
  • Consequently, we will get the below amount ($5,054). Now, we will calculate the total amount of interest we have to pay for the loan we took. For that calculation, type the following formula in Cell C11.
=C10-C6

  • Finally, hit Enter and here we have the total interest we have to pay for the above-mentioned loan.

Read more: How to Calculate Home Loan Interest in Excel


Similar Readings


2. Use Mathematical Formula to Find Gold Loan Interest

As we have mentioned earlier, we can apply a mathematical formula to calculate EMI (Equated Monthly Installment). The generic formula is:

= [ P x R X (1 + R) ^ N] / [ (1 + R) ^N – 1] 

Here,

P is the principal amount.

R is the interest rate.

N is the number of payments in the loan period.

Now, consider the previous example of the gold loan (12 months, interest rate: 2%, loan amount: $5,000) and calculate EMI.

Steps:

  • Type the below formula in Cell C9.
=(((C6*C4/12)*(1+C4/12)^C5))/(((1+(C4/12))^C5)-1)

Use Mathematical Formula to Find Gold Loan Interest

  • Hit Enter.
  • As a result, we will receive the monthly loan payment ($421.19).

Use Mathematical Formula to Find Gold Loan Interest

  • Now, to calculate the total payable amount and total interest follow the formulas mentioned in the previous method (Method 1). In conclusion, you will get the below results.

Read more: How to Calculate Interest on a Loan in Excel


Things to Remember

PMT function returns the payable amount as a negative number (red color, closed in parenthesis); which means the money is getting deducted from your bank account. To see the payable amount as a positive number, add a minus () sign at the beginning of the PMT formula.

➨ You can calculate gold loan interest using the EMI calculator too. These calculators are available on the internet too.


Conclusion

In the above article, I have tried to discuss several ways to calculate gold loan interest in excel. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Further Readings

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo