+ Reply to Thread
Results 1 to 6 of 6

Formula needs tweaking for amortizing prepaid cost

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    New Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Formula needs tweaking for amortizing prepaid cost

    Bonus $15 Paypal deposit for solution and bragging rights

    Hi Pros,

    First post
    I have a spreadsheet I inherited that already has a formula in it. Problem is it only does part of the job needed. The examples in the attached is contract coverage for 1 year and broken down in monthly columns to recognize cost. But, I need the monthly breakdowns to take into consideration partial months for the 1st and last month. The first four lines is how he formula currently works and shows it's shortcomings in the variance column L and the last 4 lines is how I use the formula with manual overwriting to get the first and last month to be accurate for partial months.

    All examples make it into the books on 09/30/12 so some line items have a "catchup" in cost recognition with coverage dates starting prior to 09/2012.

    Let me know if you need any further info.

    Thanks!!!
    Attached Files Attached Files
    Last edited by peakuse; 10-26-2012 at 02:24 PM.

  2. #2
    Registered User
    Join Date
    09-27-2012
    Location
    New Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula needs tweaking for amortizing prepaid cost

    Bump for bonus,

    Bonus $15 Paypal deposit for solution and bragging rights

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula needs tweaking for amortizing prepaid cost

    See if the attached works for you..

    Row 19-23
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    09-27-2012
    Location
    New Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula needs tweaking for amortizing prepaid cost

    You are soo close.

    Little variance on the day breakdown. For example, your line item 2(number 20 on spreadsheet)

    4 days in May/2013 is to be the last expense 4days/31days = .129 ; .129 * 134.57(monthly rate) = 17.36 whereas the formula has calculated 11.02. I know seems insignificant, but we have some very large dollars that the 4.7% variance can be significant.

    I'm assuming the variance has to do with the monthly amount and being 30/31 day months this is causing the variance, so not sure if fixable.

    Any chance you want to give it an additional try??

    Either way you deserve the $15, if you can give me an email address to send through paypal please.

    Thanks!!

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula needs tweaking for amortizing prepaid cost

    Aha..the above formula is based on the following..

    1. In case of partial first month, the daily rate (Column G) is used. In case of full month, monthly rate (Column J) is used. Monthly rate would not always equal daily rate * no. of days in month
    2. In case of partial last month, the residual amount of the net value is used.

    See if this attached version helps..
    Its all based on daily rates. Hence, monthly rates may vary based on no. of days in the month. However, in my opinion, this is a more accurate way of doing things.

    Let me know wot you think!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-27-2012
    Location
    New Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula needs tweaking for amortizing prepaid cost

    Clicked *!!

    Thanks again, see my private message.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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