+ Reply to Thread
Results 1 to 2 of 2

Need help with PMT, PPMT and IPMT functions and calcs for Loan Repayment Schedule

  1. #1
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    Excel
    Posts
    1

    Need help with PMT, PPMT and IPMT functions and calcs for Loan Repayment Schedule

    Hello I was wondering if anyone could help me with some calculations. I am creating a loan repayment schedule but cant seem to get some of these calculations working. I've attached pictures below. The total interest calc is in negative value and it shouldn't be it should be £109 instead. Also I am finding it difficult to calculate the total payment as it requires the total interest value. Also I am new to excel and I thought I would be able to drag these functions down to fill out the rest of the data although this doesn't seem to be the case and it gives me all sorts of errors.
    Attached Images Attached Images
    Last edited by hello23444; 04-16-2019 at 10:42 PM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Need help with PMT, PPMT and IPMT functions and calcs

    There is nothing wrong. Simply write =-CUMIPMT(...).

    Excel financial functions require signed cash flows: one sign for inflows; the opposite sign for outflows. It does not matter what sign we use for each direction, as long as we are consistent for each function. In fact, what we call "inflow" and "outflow" depends on your point of view: borrower v. lender.

    I might use minus PV so that the FV function returns positive, but negative FV so that the PV function returns positive.

    You write -D3 in most functions; that is, negative principal (PV; loan). IPMT and PPMT return the opposite cash flow. So they return the opposite sign: positive.

    But we must write D3 for CUMIPMT and CUMPRINC; that is, positive principal (PV). Those functions are unusual insofar as they do not permit us to enter negative principal. (A design flaw, IMHO.) So they must return the opposite sign: negative.

    Since you want to represent total interest (and all amounts) as a positive number in your tables (very reasonable, IMHO), we simply must negate the value returned by CUMIPMT and CUMPRINC.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Early loan payment using PPMT & IPMT
    By hlep in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2014, 08:28 AM
  2. Ipmt vs ispmt functions
    By Davidns in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-18-2013, 04:07 PM
  3. Using financial functions, PMT, IPMT
    By LJ25 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-11-2011, 01:14 PM
  4. How to use IPMT and PPMT to compute Interest and Investment paymen
    By William in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2006, 08:45 AM
  5. [SOLVED] How do I enter functions for an amortization schedul for IPMT and.
    By PPMT?IPMT? in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-15-2005, 12:06 PM
  6. [SOLVED] Re: IPMT and ISPMT
    By nbrcrunch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-13-2005, 06:06 AM
  7. [SOLVED] IPMT vs. ISPMT
    By MG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-08-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1