Negative nominal rate from a negative effective rate

Negative nominal rate from a negative effective rate

We recently had to create a model projecting forward 30 years and during that time some of the years had a negative property inflation rate. The assumption was an annual effective rate but the model required monthly nominal rates to create a monthly factor. Effectively we wanted to create a negative nominal rate from a negative effective rate.

The NOMINAL function in Excel does not allow for a negative rate and results in a NUM error. As a result we have to create our own formula. Based on our calculation we believe the following is correct but would appreciate feedback from experts:

Monthly Nominal Rate= 1- (1-effective rate)^(1/12)

So if we assume an effective 12% decline in property prices say (going from 1 to 0.88), then to achieve this we assume the following:

1-((1-.12)^(1/12))= 1.0596….% per month decline.

So if I start with 1 and reduce it each month by 1.0596% I end up at ≈ 0.88 which implies a drop of 12% over the year.

Is this correct? We will keep looking.

Remember we run financial modelling courses to cover these types of areas


XNPV and effective rate