Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

PPMT function in Excel

Posted on 2001-07-10
12
1,399 Views
Last Modified: 2012-05-04
Hello, Experts!

I have met PPMT function in excel. I found information ow to use it, but dunnow what this functuion presents from itself.

It would be great to hear from you the formul, how is made this PPMT.

Best regards and hope on you help,
Sandis.
0
Comment
Question by:sviksna
  • 3
  • 2
  • 2
  • +5
12 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6269970
See under PPMT and PV in Help|Content&Index: Find. Then delete _both_ questions (you posted in duplicate)
0
 
LVL 1

Author Comment

by:sviksna
ID: 6270041
Could you tell me the diference of the PPMT an VP. I am not very good at that all, so i'd realy like to hear from you that! :)

--
sandis
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6270173
sviksna,

The following two paragraphs are right out of the Help, Contents and Index description for both items from Excel.  I agree with cri that you delete this question and use the help from Excel itself to answer this question.

PPMT:  Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PV:  Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

Hope this helps.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:TrueDrake
ID: 6270207
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 6270810
There's also a fairly nice site here, though it does not contain that particular function:


http://artsci-ccwin.concordia.ca/inte/inte298s/courses/xl/xlfuns.htm
0
 
LVL 1

Author Comment

by:sviksna
ID: 6272284
Well...
I don't need to know what it retunrs and how to use it.. i wana know the formula it presents inside this PPMT!

let's say so.. function myCalculations(rate, value, count) needs 3 (PPMT also is function also got n parameters) parameters and it looks in action like myCalculations(45%, 1/2, 45);

so, you get output, say 34% from value;

but how we get it???????

we get it becouse

myCalculations(rate, value, count)
begin
myCalculations.value_to_return =  value / count * rate
end


"value / count * rate"

so... THIS IS the FORMULA of myCalculations!!!


I wana know, what operations are made and what formula is hidden in the PPMT function!!!

That is the anwer i'd like to hear!?!?



0
 
LVL 1

Expert Comment

by:jelen
ID: 6274741
This question is better suited for a Finance forum in Experts-Exchange!

Here is pseudocode to show roughly how ppmt works

Function ppmt(Rate, Per, NPer, PV)
' Rate = Interest rate per period
' Per = Which period you want to calculate principal payment
' Nper = # Periods in the annuity or loan
' PV = Present Value of the annuity or loan

' Calculate the payment per period
ThisPmt = Application.WorksheetFunction.Pmt(Rate, NPer, PV)

' Loop through each year up to Per
For i = i to Per
     ' Calculate the Interest portion for this period
     Int = Rate * PV
     ' Calculate the Principal portion for this period
     Prin = ThisPmt - Int
     ' Calculate the new PV at the end of Period I
     PV = PV - Prin
Next i

PPmt = Prin

End Function

Bill
0
 
LVL 1

Author Comment

by:sviksna
ID: 6276058
Bill,
thanx for VB code but i need it for PHP :)

Maybe you could tell me where is that Finance forum?
0
 
LVL 1

Accepted Solution

by:
jelen earned 100 total points
ID: 6276349
Sorry - it is a mythical finance forum ;-)

Check out the code snippet forums at PHPBuilder.com  http://www.phpbuilder.com/snippet/detail.php?type=snippet&id=96

This code will get you the monthly payment amount.  

I don't know PHP.  If I can convert the VBA to English, can you convert it to PHP?

Monthly Payment = Result from above code snippet
Monthly Interest Rate = Annual Interest Rate / 12
Present Value = Amount Financed from code snippet
Loop from 1 to the period number in question
  Calculate the Interest for this Period as Present Value * Monthly Interest Rate
  Calculate This month's principal payment as Monthly Payment - Interest for this Period.
  Calculate Present Value as Present Value - This months principal payment
End Loop

Display that the PPMT is This Months Principal Payment from the last iteration of the loop.




0
 
LVL 2

Expert Comment

by:TrueDrake
ID: 6277187
Hi,

The payments are calculated by iteration rather than by a direct formula; take a formula for any of the annuities or perpetuities and plug in the other variables until you get a result. If you do this by binary iteration (dividing the remaining data in half), you should get a good result in ten tries. Note that Excel has a default of 10 iteration tries in its options"
Since the payments include interest that is usually variable depending on the specific date, you should be able to calculate it for that specific date, then simply substract the interest portion and you will get the principal portion of the payment.

ex. Future Value of an annuity due (use & for an exponent):
FVA=PMT[((1+r)&n - 1)/r]&(1+r)

Enjoy
0
 

Expert Comment

by:costello
ID: 6415660
If no objections are made in the next 5 days, I will force accept the contribution by jelen.

costello
Community Support Moderator @ Experts-Exchange

P.S. This post is made as part of the general effort to clean up this topic area, meaning, looping through all the open questions with no activity since 21 days.
0
 

Expert Comment

by:ComTech
ID: 6690958
Answer accepted by:

ComTech
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question