Sign in to join the conversation:
Is there a similar function to PMT? =PMT(rate, period, pv, fv) etc? Have not been able to find one. Thank you.
Hi David—
There isn't a similar function to PMT in Smartsheet.
You may need to consider storing the values (rate, nper, pv, [fv], [type]) in specific cells and then manually calculate with a formula.
This is equation with 4 columns (ignoring two optional args in PMT)
=(Pv@row * R@row / IntPer@row) / (1 - (1 + R@row / IntPer@row) ^ (-n@row * IntPer@row))
R = APR %
Pv = present value
IntPer = interest periods per year
n = term in years
Craig
Hi All,
I have the same requirement to re create the excel formula "PMT". The above formula does not include the future value and type aspects.
Also the Amortisation Template in SS doesn't line up with the required calculations either.
Trying to build a quote calculator for our customers to use.
The file attached shows the working out of the Excel formula.
Excel formula =PMT(rate,nper,PV,[FV],[Type])
The PMT function syntax has the following arguments:
Any help would be greatly appreciated!!!
This has ALREADY been solved by Mr Williams above but thought this might be useful for someone else to visualise it...
Using the above I initially created helper columns named the same as the references in the formula
Then realised what it was doing and made the following formula from the entries in a single column called "Loan Values":
=ROUNDUP(SUM(($[Loan Values]$4 * [Loan Values]2 / 12) / (1 - (1 + [Loan Values]2 / 12) ^ (-$[Loan Values]$3 * 12))), 2)
(Works with or without absolute references "$". The Yellow cells are my User Entry variables. Work in progress)
The "12" is the number of months in a year just in case that wasn't clear.
Also, I used another one of Mr Williams' tips from another thread for the Date. Using a Date column type on the far left, I then used:
=([Start Date]@row + "")
This displays the "date" as plain text in my loan values column. The end date is then worked out automatically using this formula:
=([Start Date]7 + SUM([Loan Values]3 * 365) + "")
Why? because when I create a report for display on dashboards I will exclude the Date Column for a neater look.
ROUNDUP to 2 places is also used BUT not needed really.
The layout is inspired by a free Microsoft template
Nice work @ON_Again
@J. Craig Williams
I don't suppose you happen to have a Smartsheet solution set up for present value (PV). I'm looking at this excel explanation and am at a loss of how to recreate it in Smartsheet.
Thanks,
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…
Hello, i've a question about 2 tasks. Task 1 has for example 4 hours and should be on day 1 for example 6 january. Task 2 has 30 hours and should be on start on 4 january and futher. How can i manage that on 6 january task 2 has only 4 hours? Now i got the message user is over-allocated because task 2 is planned on a full…
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…
