Excel in Personal Finance - Part 5 - Function 'PMT'

Let us learn 5 Excel functions that are useful for personal finance

Click below to read the following:
Introduction to Excel in Personal Finance

Excel Part 1 - Function PV

Excel Part 2 - Function FV
Excel Part 3 - Function EFFECT
Excel Part 4 - Function XIRR

Function 5 - PMT

Saving money in monthly installments (SIP)

After five years, Karthik plans to spend about 15 lakhs of rupees for holiday trip with family to Europe. How much money should he invest in monthly installments now? 

Let us compute returns using function PMT.

Used factors / variable:

Information to input: 

Let us find how much Karthik has to save every month
Variable	Formuala	Inputs	Units	Remarks
Known Variable
Rate	Rate /12	0.8%	%	Converting Annual rate avilable in to monthly for this calculation
Nper	5*12	60	Months	Interval period - here it is months
PV		0		Intial payment - Here it is zero - no down payment or intial savings
FV		1500000	Rs	Fianl amount required for our trip here
Type/		0		Interest at the end, taken as zero
Assumed variable
Expected return in %		10%	%	Assumed as 10% returb
Calculated value
Pmt		?	Rs	SIP amount we need to save every month for this trip

You can see the picture below to understand and to enter the data in Excel. In your computer, the cell number / reference may be different, but the input information must be exactly as shown in the picture with respect to variable as given in the problem.

For the visit to Europe after five years with his family, Karthik must invest Rs 20,000 in monthly installments so that he can have around 15 lakhs after 5 years on the assumption that he will get 10% return on his investments.

Click the link to access the working model of the Payment template  in google sheets - You are free to change the variable and get the desired results. You can download and share with your contacts who are interested in this functions / learning excel 

Click here to read the PREVIOUS article about using XIRR function: The SBI Magnum multi-cab scheme is taken as an example and the period of investment is 4 years. Two types of calculation are used here. (A) When the investment is irregular. Usually most of our investor investments are in this type. (B) When the investment is regular.