Most gold loan spreadsheets multiply the weight by the rate, take 75% and stop there. Since 1 April 2026 that misses three things: the RBI limit is no longer a flat 75% for smaller loans, the rate used to value your gold is fixed by rule, and the way you repay changes both the interest and how much you can borrow. This sheet handles all three with live formulas you can check.
What the sheet does
- Values your gold from its net weight and purity (24, 22 or 18 carat), at the lower of the 30-day average and yesterday's closing rate.
- Works out the most RBI allows on that gold, and the amount at your own lender's LTV, whichever is lower.
- Puts three repayment styles side by side for the same loan: EMI, bullet (everything at the end) and interest every month with the principal at the end.
- Shows the total interest, the processing fee with GST, and the full cost of each style.
- Checks a bullet loan against the RBI rule that counts the interest due at maturity, and gives the largest bullet loan that fits.
- A second tab shows what you pay in each month under each style, for up to 36 months.
- A plain .xlsx with no macros. It opens in Excel, Google Sheets and LibreOffice, licensed CC BY 4.0.
The RBI rules the sheet uses
- Loan-to-value limit by the borrower's total consumption gold loan: up to 85% for loans up to ₹2.5 lakh, 80% above ₹2.5 lakh up to ₹5 lakh, and 75% above ₹5 lakh. It applies to banks, NBFCs and co-operative banks alike.
- Valuation: the rate for your gold's actual purity, taking the lower of the average closing price of the last 30 days and the previous day's closing price, as published by IBJA or a SEBI-regulated commodity exchange.
- Bullet loans: the LTV is measured on the total amount repayable at maturity, principal plus interest, and a bullet loan for personal use can run for 12 months at most.
- The limit has to hold for the whole loan, not only on the day you borrow. If the gold price falls, the lender can ask you to pay part of it back or add gold.
- Once you repay in full, the lender must return your gold within seven working days, or pay you ₹5,000 for each day of delay that is its fault.
Source: RBI (Lending Against Gold and Silver Collateral) Directions, 2025 (paras 6, 15, 17, 19, 20, 35, 46)
20 grams of 22-carat jewellery, worked through
Take a 24-carat rate of ₹13,500 a gram as the 30-day average (an example: put in the day's IBJA figure). The pure gold in 20 grams of 22-carat jewellery is worth ₹2,47,500. RBI would allow up to ₹2,10,375 on it, the 85% band. At a lender LTV of 75% the loan is ₹1,85,625. Over 12 months:
- EMI at 10.25%: ₹16,341 a month, ₹10,467 of interest in all.
- Bullet at 9.30%, simple interest: ₹2,02,888 due at the end, ₹17,263 of interest.
- Interest every month at 10.25%: ₹1,586 a month, then the ₹1,85,625 principal in month 12. Interest: ₹19,032.
- A 0.25% processing fee with 18% GST adds ₹548 to each.
The bullet loan carries the lowest rate and still costs more than the EMI, because the whole principal stays outstanding for the full year while an EMI shrinks it every month. Paying interest monthly costs the most of the three. The interest-only style keeps the monthly payment small, but only if you can be sure the lump sum will be there in month 12.
Gold loan calculator: try other weights and ratesSBI gold loan: the rates in the sheet
The sheet carries SBI's Personal Gold Loan terms as a reference table, from SBI's page last updated 17 October 2026. SBI quotes a margin, the share of the gold's value it keeps back, so the LTV is 100% minus the margin.
- 6-month bullet: 9.20%, margin 30% (LTV 70%).
- 12-month bullet: 9.30%, margin 35% (LTV 65%).
- EMI loan: 10.25%, margin 28% (LTV 72%), up to 36 months.
- Overdraft with interest paid monthly: 10.25%, margin 26% (LTV 74%), from ₹5 lakh.
- Minimum loan ₹50,000. Processing fee 0.25% of the loan up to ₹2 lakh and 0.15% above, plus GST, with minimums.
Source: SBI Personal Gold Loan: features, repayment and rate of interest (sbi.bank.in)
To run the SBI numbers, put the LTV of the scheme you want in the lender cell and its rate in the matching repayment column. SBI's rates are set as a spread over its MCLR, so they move when the MCLR does; check the page before you borrow.
The formulas, if you build your own
The MAX in the RBI line matters: a loan of exactly ₹2.5 lakh is allowed under the 85% band, so gold worth a little more than ₹2.94 lakh never gets less than ₹2.5 lakh just because 80% of it falls below that figure.
What the sheet does not do
- It does not know about your other gold loans. The RBI band depends on your total consumption gold loans, so add those in your head if you have any.
- It does not model part-payments, penal charges or renewal fees. Read the sanction letter for those.
- It values the gold content only. Stones, wax and thread are not counted, and a lender's assayer may find a lower purity than the hallmark suggests.
- Rates at NBFCs and other banks vary widely. Put in your lender's figures.
Frequently asked questions
- How is gold loan EMI calculated in Excel?
- With the PMT function: =PMT(annual rate/1200, months, -loan amount). The sheet does this, and puts the bullet and interest-only versions of the same loan next to it.
- What is the maximum LTV on a gold loan in 2026?
- Under RBI directions in force from 1 April 2026: 85% for loans up to ₹2.5 lakh, 80% above ₹2.5 lakh up to ₹5 lakh, and 75% above ₹5 lakh. Your lender can offer less.
- Is a bullet gold loan cheaper than EMI?
- Not usually, even at a lower rate, because you pay interest on the full amount for the whole term. RBI also counts the interest due at maturity against the LTV limit, so a bullet loan can be smaller.
- Can I use the sheet for an SBI gold loan?
- Yes. It has SBI's current rates and margins for each scheme as a reference table; enter the scheme's LTV and rate in the yellow cells.
- Does the sheet work in Google Sheets?
- Yes. Upload the .xlsx to Google Drive and open it with Google Sheets; the formulas carry over.