+ Reply to Thread
Results 1 to 11 of 11

LF Help with APR formula

  1. #1
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    LF Help with APR formula

    how do I calculate the APR on my loan if I have the following information:

    1) date loan started
    2) principal amount of loan advanced
    3) accrued interest to date
    4) compounded value of loan (principal plus interest)
    5) The number of days outstanding
    6) how do I determine the APR being charged

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: LF Help with APR formula

    What about the future interest rate? Where is that?

  3. #3
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    Re: LF Help with APR formula

    thanks for your assistance, here's a picture of what I'm trying to solve for

    http://prntscr.com/gk17dl

    my formula is incorrect:

    =((J3/I3)*365)/F3*100

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: LF Help with APR formula

    Please don't upload pictures they are rarely much use and none of us want to recreate a workbook you already have. So upload your workbook.

    You say your formula is incorrect which implies you know the required answer, so tell us what you expect.

  5. #5
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    Re: LF Help with APR formula

    I was told the interest rate is 2.30% compounded monthly. If this is correct, I would expect the annualized APR should be about 31.1% but I can't seem to get that number.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: LF Help with APR formula

    ...the workbook???

  7. #7
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    Re: LF Help with APR formula

    Here is the workbook with my attempts to solve for the APR on the data tba (see P3)
    Attached Files Attached Files

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: LF Help with APR formula

    Taking file 18391 as an example is the Accrued amount the chargeable interest for a complete year?
    What about PPSA and Other charges? Shouldn't these feature in any APR calc?

  9. #9
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    Re: LF Help with APR formula

    you are correct, the PPSA and other charges should be added to get the true APR, however, in this instance I'm trying to create a simple formula that may be applied across all the loans.

    The formula is used was: =RATE(F3,-J3,I3)

    for file#18391 = 33.66% which looks reasonable however,
    for file#18372 = 3.69% which does not look reasonable

  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: LF Help with APR formula

    I still don't understand the time element.
    Does column J, i.e the Accrued amount represent the interest paid in a year?

    i.e. with file 18391 is $141.47 the total interest paid in a year on a loan of $1750 which is then paid off by the end of the year?
    If so and ignoring any monthly compounding effect then the interest rate is 8.08%

    It's not yet clear to me exactly what you're trying to demonstrate or how, if at all, the number of days in the workbook are of any relevance.

    Can you clarify please

  11. #11
    Registered User
    Join Date
    09-11-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    2017
    Posts
    7

    Re: LF Help with APR formula

    I'm attempting to validate the interest rate charged on a file. We were provided with several data points however we can't seem to validate the formula.

    For example:
    File#1831
    1) advance date: Sept 7-17
    2) the initial amount of the loan was $1750
    3) accrued interest to Sept 11-17 = $141.47
    4) principal plus interest to Sept 11-17 = $1891.47
    5) we are told the interest rate charged is 2.3% per month compounded monthly
    6) we are trying to solve for the rate inorder to determine what rate was charged to get $141.47

+ 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. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  2. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  3. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

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