How to Calculate the Gold Loan Interest in Excel – 2 Methods

Method 1 – Using the Excel PMT Function to Calculate the Gold Loan Interest

This is the syntax of the PMT function:

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

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.

You have taken a 1-year gold loan from a bank with an interest rate of 2% and a loan amount of $5,000. To calculate the monthly loan payment for the borrowed amount and find the total interest:

Steps:

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

Excel PMT Function to Calculate Gold Loan Interest

In the PMT formula, the interest rate is divided by 12 to calculate monthly payments.

This the monthly payment.

Excel PMT Function to Calculate Gold Loan Interest

=C9*12

Excel PMT Function to Calculate Gold Loan Interest

  • Press Enter.

The output is $5,054

  • To calculate the total amount of interest, enter the following formula in C11.
=C10-C6

  • Press Enter to see the result.


Method 2 – Using a Mathematical Formula to Find the Gold Loan Interest

The generic formula is:

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

P is the principal amount.

R is the interest rate.

N is the number of payments in the loan period.

Consider the example in the previous method (12 months, interest rate: 2%, loan amount: $5,000) and calculate the EMI.

Steps:

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

Use Mathematical Formula to Find Gold Loan Interest

  • Press Enter.

The monthly loan payment  is $421.19.

Use Mathematical Formula to Find Gold Loan Interest

  • To calculate the total payable amount and total interest use the formulas in Method 1 to see the results.

Read More: How to Calculate Principal and Interest on a Loan in Excel


Things to Remember

The 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 the gold loan interest using the EMI calculator.


Download Practice Workbook

Download the practice workbook here.

 


Related Articles


<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo