+ Reply to Thread
Results 1 to 11 of 11

Problem with formula that doesn't catch the last values in a row sometimes

  1. #1
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Problem with formula that doesn't catch the last values in a row sometimes

    Greetings,

    I'm having a problem with a sheet that is calculating payment and interest for a given loan. When I change the interest rate to 2%,4%,7%,9%,11%,14%,16% and 17%, I don't get a value in the last row which is B32,C32,D32,E32.

    B32=IF(A32>$D$14;"";$D$13)
    C32=IF(A32>$D$14;"";$D$8/12*E31)
    D32=IF(A32>$D$14;"";B32-C32)
    E32=IF(A32>$D$14;"";E31-D32)

    I found the file on google when I searched for "amortization formulas" from a website called Vertex42, under "Example: Loan Amortization Formulas in Excel". The forum doesn't let me upload yet since im a new member. Or you can just view it online to get a picture of my problem.

    Is it correct that the last amount shouldn't be included? Is the file right or wrong? If it is wrong, how shall I adjust for this?

    Best regardsSkärmklipp1.JPG
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    Since the loan balance is 10 512,34 when you have made your 11th payment, there should be one more final payment to pay off the remaining debt of 10512,34.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    A non-editable picture of an Excel sheet is of limited (no...) use.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    I hope it works now!

    Please find the attached excel file
    Attached Files Attached Files

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    Try in D14
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    José, why do you add 10 in the formula?

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    Hi

    The rounding that is being done is only intended to eliminate calculation errors. The NPER function determines (non-exact) values with 15 significant digits, and the user expects (probably) integer values. Using 10, 1, or 0 decimal places does not matter. What matters is to eliminate Excel's own truncation errors.

  8. #8
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    José, first of all, thank you. For my understanding, what happened in this case that made the formula not display the value in row 12? It had something to do with the rounding then or?

    Best regards

  9. #9
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    You are welcome.

    Yes. The value you get in D14 is 11,9999999999997000 (15 significant digits) (125.000 2% 1 Year) that is <12.
    This value comes from D13 = $10.529,85841 <> $10.529,86.

    Regards

  10. #10
    Registered User
    Join Date
    11-05-2018
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    28

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    Okay, that's what I had in mind when I saw the error but couldn't really see where I should have started. Thanks once more for solving my problem. Have a great weekend José!

  11. #11
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Problem with formula that doesn't catch the last values in a row sometimes

    You are welcome.

    Regards

+ 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. My formula set doesn't work, can't see any problem. HELP!
    By GrFerg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2018, 03:59 PM
  2. When "IsError" doesn't catch error
    By shawnvw in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-21-2015, 12:57 PM
  3. formula that doesn't include 0 values
    By amartino44 in forum Excel General
    Replies: 1
    Last Post: 12-06-2013, 03:09 PM
  4. help with fisheries catch formula
    By Joel Metcalfe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2012, 02:31 AM
  5. Replies: 6
    Last Post: 08-22-2006, 08:15 AM
  6. How can I catch a Paste action and default it to PasteSpecial-Values
    By srinu1264 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-23-2006, 09:20 AM
  7. [SOLVED] Catch _AfterSave Event & Saved Property Problem ...
    By Joe HM in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-17-2005, 08: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