NOMINAL Function in Google Sheets: Formula, Examples & Troubleshooting

=NOMINAL(effective_rate, periods_per_year)

Both inputs are required. The first is an annual effective rate; the second is the number of compounding periods in one year. Google’s NOMINAL reference documents that direction of conversion. It does not supply an optional payment, principal, or term argument.

The corresponding mathematical rate conversion is m*((1+effective_rate)^(1/m)-1), where m is periods per year. NOMINAL returns the annualized nominal quote. Divide that result by m to obtain its periodic rate.

Enter this small model:

1. Enter =NOMINAL(A2,B2) in C2. Expected result: 20%.
2. Enter =C2/B2 in D2. Expected result: 10% per half-year.
3. In E2 enter =(1+D2)^B2-1. Expected result: 21%.

The independent check is 1.1*1.1 = 1.21. Two successive 10% increases produce a 21% annual increase, while the nominal annual quote is 2*10% = 20%. The extra percentage point arises from compounding.

For a starting value of 1,000, the two period-end values are 1,100 and 1,210. This is a synthetic no-cash-flow illustration, not a statement of available investment terms.

Keep the effective annual rate at 21%, but compare frequencies 1, 2, and 4. Use =NOMINAL(21%,1), =NOMINAL(21%,2), and =NOMINAL(21%,4) in separate cells.

Expected nominal annual rates are 21%, 20%, and approximately 19.5235392681%. The associated periodic rates are 21%, 10%, and approximately 4.8808848170%.

All three were constructed to compound to the same 21% effective annual result. The smaller nominal quote is not evidence of a lower effective cost or return unless compounding frequency is also held constant.

Put 6% in A5 and 12 in B5. =NOMINAL(A5,B5) in C5 has expected result approximately 5.8410606784%. In D5, =C5/B5 gives approximately 0.4867550565% per month.

To check a one-year projection from 5,000 with no intervening cash flows, enter =5000*(1+D5)^12. Expected result: 5300, since the original annual effective rate was 6%.

Simply using 6%/12 = 0.5% each month produces approximately 5308.3890593225, which compounds to more than 6% annually. Dividing by twelve is appropriate for a nominal annual rate with monthly compounding; it is not the same operation for an effective annual rate.

Check whether the source quote is actually nominal or effective before selecting the formula. A label such as “annual rate” without its convention is insufficient for an unambiguous conversion. Store the frequency alongside the converted rate rather than leaving the new nominal quote detached from its meaning.

Use a rate cell containing 0.21 or 21%, not the integer 21. Ensure periods per year is the count 12 for monthly compounding. Applying percent formatting to a count would turn 12 into 1200%, obscuring the input.

The short Google page does not establish a complete error-code table, rounding/truncation behavior for noninteger frequencies, or every zero/negative-rate boundary. The reproducible examples use positive rates and positive integer frequencies; do not assume another program’s edge-case contract applies. If the target workbook produces an error, simplify to =NOMINAL(21%,2) and inspect the input cell types and locale.

Keep precision in the periodic rate. A rounded monthly rate of 0.49% will not exactly reconstruct 6% after twelve periods. Display rounding and formula rounding are separate choices.

NOMINAL is in Google’s current rolling-service documentation. First introduction, last functioning release, and retirement are not established by the reviewed sources. The guide does not certify cross-application execution or calculate fees, taxes, or regulatory disclosure measures.

Related published guides: POWER for the round-trip calculation and ROUND for controlled rounding.

Reviewed October 17, 2026: NOMINAL and EFFECT. Numerical examples are independently calculated.