• Baffled by financial functions

    From Philip Herlihy@21:1/5 to All on Mon Jun 28 23:36:34 2021
    XPost: microsoft.public.excel.worksheet.functions

    We're considering a loan for a car - a "Personal Contract" where you essentially pay off the depreciation for four years and then have a choice about whether to buy what's left or hand it back. I can't get any of the Financial functions to give "sensible" answers!

    I understand it's essentially two loans: one where you make 47 fixed monthly payments (in this case 229.00) with an interest rate (annual, calculated monthly) of 4.84% until you've paid off 8,786.00, leaving 5,927.00 to pay if you want to keep the car rather than hand it back. And you pay the same interest rate throughout (without making any payments) on that 5,927.

    What function will do this for me? I've looked at CUMIPMT, FV and PV, but I can't make any of them produce a sensible figure!

    Duration (Months) 48
    47 monthly payments of 229.00
    Manuf. deposit contribution 2,250.00
    Customer deposit 2,091.11
    Total deposit 4,341.11
    Optional extras 595.00
    Retail cash price (inc. paint) 19,055.00
    Amount of credit 14,713.89
    Optional final payment 5,927.40
    Total amount payable 21,041.51
    Option to purchase fee 10.00
    Rate of interest 4.84%
    Representative APR 4.90%

    If you take the Retail Cash Price from the Total Amount payable you get a figure for Interest of 1,986.51 - but of course that depends on the quoted figure (opaque) of Total Amount Payable.

    Going in circles, here....

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Mon Jun 28 23:39:00 2021
    XPost: microsoft.public.excel.worksheet.functions

    In article <MPG.3b445f49bac96a88989959@news.eternal-september.org>, thiswillbounceback@you.com says...

    We're considering a loan for a car - a "Personal Contract" where you essentially pay off the depreciation for four years and then have a choice about whether to buy what's left or hand it back. I can't get any of the Financial functions to give "sensible" answers!

    I understand it's essentially two loans: one where you make 47 fixed monthly payments (in this case 229.00) with an interest rate (annual, calculated monthly) of 4.84% until you've paid off 8,786.00, leaving 5,927.00 to pay if you want to keep the car rather than hand it back. And you pay the same interest rate throughout (without making any payments) on that 5,927.

    What function will do this for me? I've looked at CUMIPMT, FV and PV, but I can't make any of them produce a sensible figure!

    Duration (Months) 48
    47 monthly payments of 229.00
    Manuf. deposit contribution 2,250.00
    Customer deposit 2,091.11
    Total deposit 4,341.11
    Optional extras 595.00
    Retail cash price (inc. paint) 19,055.00
    Amount of credit 14,713.89
    Optional final payment 5,927.40
    Total amount payable 21,041.51
    Option to purchase fee 10.00
    Rate of interest 4.84%
    Representative APR 4.90%

    If you take the Retail Cash Price from the Total Amount payable you get a figure for Interest of 1,986.51 - but of course that depends on the quoted figure (opaque) of Total Amount Payable.

    Going in circles, here....

    Nb crossposted to:
    microsoft.public.excel.misc and
    microsoft.public.excel.worksheet.functions.
    I tried to set a "followup to", but my newsreader threw up errors -sorry!

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)