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