Wednesday, December 03, 2008   
  Search   
 
 
Register  Login  
Forums  
     Minimize  

Welcome to MeraWindows forums.

Thank you for being at the Microsoft Windows Community Site. You may have to register before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
     
  


 
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Formula for arriving at Equated Quarterly/ Halfyearly/Yearly Instalments
Previous Previous
 
Next Next
New Post 9/26/2008 9:10 AM
User is offline Shashank
2 posts
Member


Formula for arriving at Equated Quarterly/ Halfyearly/Yearly Instalments 

I am looking for a solution in Excel as to how to arrive at Euquated Quarterly/ Half-yearly/ Yearly Instalments. Ex:

X has raised a loan of Rs 10.00 lakh @ 10% interest per annum  repayable in 5 years in  20 equal quarterly instalments with effect from 1 year after the date of availment of the loan. How to put the formula in Excel.
 
New Post 9/26/2008 7:55 PM
User is offline Ashish Mathur
89 posts
www.ashishmathur.com
Member




Re: Formula for arriving at Equated Quarterly/ Halfyearly/Yearly Instalments 

-PMT(C3/VLOOKUP(C4,G2:H5,2,0),C2*VLOOKUP(C4,G2:H5,2,0),C1)Dear  Shashank,

Assume that your data is set up as follows in range A1:C3

Amount of Loan  1,00,000.00
Period of loan 5
Interest rate (%) 14%

In cell A4, enter "Period of repayment".  IN range G2:H5, enter the following:

Monthly 12
Quarterly 4
Half yearly 6
Yearly 1

In cell C4, Go to Data > Validation and in the drop down select List and define the range as G2:G5

In cell C6, enter the following formula = -PMT(C3/VLOOKUP(C4,G2:H5,2,0),C2*VLOOKUP(C4,G2:H5,2,0),C1)

Now you can see the annuity by selecting the period in the drop downbox in cell C4.

Hope this helps.  Pease let me know how this works for you.

 

For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com
 
 Page 1 of 1
Previous Previous
 
Next Next
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Formula for arriving at Equated Quarterly/ Halfyearly/Yearly Instalments


   Get Your Own E-Mail Account @MeraWindows.com Minimize  
New Page 1 New Page 1
Show your cool quotient with @merawindows.com email account
     
  
 
 
Terms Of Use   Privacy Statement  
© 2008 MeraWindows.com