• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reversing Excel PMT formula

D

Donnel

New Member
Hi there!

I have an excel sheet which uses the PMT formula but i need to change it because one of the independent variables has now become the dependent one. That is, it is now the required output.

In brief, is there a way to reengineer the following formula so that I get the value of x? All variables other than x are known.

c = PMT(r,t,x,ax) + bx

Thank you!
 
In the simplified formula that I have posted above, a, b, r and t are known constants. c is the output and x is the input. I want to reverse it so that i can get x if i input c.

As i said, i posted a simple formula to enquire about the method. I am attaching a demo sheet here which is a little more detailed and closer to what I am actually doing. In essence i want to switch the input and the output.

Thanks.
 

Attachments

  • Demo sheet for forum.xlsx
    13.9 KB · Views: 2
@Donnel

Can you try below formula to find "X" (present value) if PMT, i and N are given:

PVRegularAnnuity.png


Regards,
 
Hi Somendra,

The formula you suggested doesn't have any variable for residual value. Do you think it applies to the situation here?
 
Somendra Misra said:
@Donnel

Can you explain your file. You file has a PMT function in C10. Can you explain more.

Regards,
Click to expand...
Hi,

The formula in C10 can be replaced by C8 + C9. After that, I would just say that the worksheet contains the actual problem that I am solving, rather than trying to map it to the equation, which as i said was a simplified way of describing it.

I am reattaching the sheet after making this change. So now we have a sheet in which starting with the value in the blue cell ("Cost") we arrive at the value in the orange cell ("Monthly Depreciation"); and my objective is to have a sheet if we were to put a value in the orange cell, we would get back the value in the blue cell (with the other constants such as rate and years remaing the same).

Thank you.
 

Attachments

  • Demo sheet for forum.xlsx
    15.1 KB · Views: 5
Hi Donnel:
As per my undertsnading you want to arrive at PV using the PMT If your
Monthly payment towards annuity is an input value it can be calculated by using the built-in excel PV function. in your eaxample u will arrive at
-$540,000.00 using this formula =PV(C6/12,C5*12,C9,C7,0) reduce your surcharge u will arrive at the cost of 500,000, Hope this helps.
 
Hi Nebu,

Thanks for the reply. The cells C7 and C9 and C3 (the surcharge) are all depending on C2, hence they can't be used in the formula I want. I need to start at C10 and arrive at C2, referencing values only C10, C6 and C5. I must not refer to cells C3, C4, C7, C8 and C9. Thanks.
 
It is highly unlikely that you can arrive at present value just by using Interest rate and no of years and Monthly depreciation..
 
Hi Donnel ,

If you had a standard PMT formula such as the following :

=PMT(C6/12,C5*12,-C4,0,0)

the following formula would be the exact reverse of it :

=PV(C6/12,C5*12,-C9)

If you now include a residual value component , and change your PMT formula to :

=PMT(C6/12,C5*12,-C4,C7,0)

the corresponding PV formula would be :

=PV(C6/12,C5*12,-C9,-C7,0)

Once you add elements such as insurance and surcharge , which are not dependent on the PMT formula , there cannot be a way to use the PV formula itself in any way to get back the original value of C4 , leave alone the value in C2. Only by including references to C3 and C8 will it be possible.

Narayan
 
NARAYANK991 said:
Hi Donnel ,

If you had a standard PMT formula such as the following :

=PMT(C6/12,C5*12,-C4,0,0)

the following formula would be the exact reverse of it :

=PV(C6/12,C5*12,-C9)

If you now include a residual value component , and change your PMT formula to :

=PMT(C6/12,C5*12,-C4,C7,0)

the corresponding PV formula would be :

=PV(C6/12,C5*12,-C9,-C7,0)

Once you add elements such as insurance and surcharge , which are not dependent on the PMT formula , there cannot be a way to use the PV formula itself in any way to get back the original value of C4 , leave alone the value in C2. Only by including references to C3 and C8 will it be possible.

Narayan
Click to expand...

Hi Narayan,

Thanks for your reply. The thing is that I won't know the residual value unless i know the value in C2 in the first place.

The way I see it is: It is possible to arrive at C10 using only the values in C2, C5, and C6 (the other cells hold only intermediate values). So in principle it should be possible to arrive at C2 using only C10, C6 and C5. The number of variables is the same (4), only the dependent variable is now treated as independent and a different one becomes the dependent variable, so mathematically speaking it only ought to be a question of manipulatig the equation.
 
Hi Donnel ,

It is true that you have arrived at C10 using only the values in C2 , C5 and C6 ; the initial question was whether there was a reverse formula for PMT.

Thus , if you start with C10 , and implement the same logic in reverse , you can obviously arrive at C2.

However , you have put the constraint that use cannot be made of the intermediate results ; with this constraint , how are we going to implement the logic in reverse ?

Given C10 , we need to derive C9 , C8 , C7 , C4 and C3 in order to get C2. There is no logic that can frogleap these steps to arrive at C2 directly.

Narayan
 
You must log in or register to reply here.
Back
Top