How to calculate standard precipitation index in excel?

You can see SPI calculation for Mashhad station in attached file. You can compare this file by the output of MDM(Meteorological Drought Monitor) or by RDIT(Rain-based Drought Indices Tool) or by DMAP(Drought Monitor And Prediction) or by Drought Mitigation Center software for spi. It's same as RDIT and MDM and excel file.

For knowing SPI (Standard Precipitation Index), now it is better to review some concepts about SPI (Standard Precipitation Index). To calculate the SPI (Standard Precipitation Index), a long-term precipitation record at the desired station is first fitted to a probability distribution (e.g. gamma distribution), which is then transformed into a normal distribution so that the mean SPI is zero (McKee et al., 1993, 1995). The SPI ( Standard Precipitation Index) may be computed with different time steps (e.g. 1 month, 3 months, 24 months). Guttman (1998) showed that the use of SPI at longer time steps was not advisable as the sample size reduces even with originally long-term data sets. The use of different timescales allows the effects of a precipitation deficit on different water resource components (groundwater, reservoir storage, soil moisture, streamflow) to be assessed.

SPI (Standard Precipitation Index) has several advantges, such as uses precipitation only; can characterize drought or abnormal wetness at different time scales which correspond with the time availability of different water resources (e.g. soil moisture, snowpack, groundwater, river discharge and reservoir storage), More comparable across regions with different climates than the Palmer Severity Drought Index (PDSI), Less complex to calculate than the PDSI.

Also, SPI has several limitations, e.g. As a measure of water supply only, the SPI (Standard Precipitation Index) does not account for evapotranspiration, and this limits its ability to capture the effect of increased temperatures (associated with climate change) on moisture demand and availability, Sensitive to the quantity and reliability of the data used to fit the distribution; 30-50 years recommended Does not consider the intensity of precipitation and its potential impacts on runoff, streamflow, and water availability within the system of interest.

Positive SPI (Standard Precipitation Index) values indicate greater than mean precipitation and negative values indicate less than mean precipitation. The SPI may be used for monitoring both dry and wet conditions. The "drought" part of the SPI range is arbitrarily split into "near normal" (0.99 > SPI > -0.99), "moderately dry" (-1.0 > SPI > -1.49), "severely dry" (-1.5 > SPI > -1.99) and "extremely dry" (SPI less than -2.0) conditions. A drought event starts when SPI value reaches -1.0 and ends when SPI becomes positive again. The positive sum of the SPI for all the months within a drought event is referred to as "drought magnitude".

Name: alireza