How to Use an Excel Formula for Loan Repayment Based on a Drop-Down Selection in WPS Office
Question details
The user needs a WPS Office spreadsheet formula for cell C2 that calculates an annual student-loan repayment from the gross salary in A2 and the repayment plan selected in B2, applying the correct earnings threshold and repayment percentage, or returning zero when no plan condition is met.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating annual student loan repayments using a salary cell and a repayment-plan drop-down list.
- Observed behavior
- The formula should return the annual repayment amount for Plan 1, Plan 2, Plan 4, Plan 5, or Postgraduate Loan when the salary exceeds the corresponding threshold, otherwise zero.
Make sure A2 contains a numeric gross annual salary and B2 contains one of the exact dropdown values: “Plan 1”, “Plan 2”, “Plan 4”, “Plan 5”, or “Postgraduate Loan”. If the dropdown is not yet created, add it with Data > Validation before entering the formula.
Use the Nested IF Formula in C2 to Calculate the Repayment
Use this solution when you want to keep the calculation in a single formula and return zero automatically when the selected plan does not apply.
The formula checks the plan in B2 and the salary in A2 against each plan threshold. When the salary is above the threshold, it multiplies the salary by the plan’s repayment rate; otherwise it continues to the next condition and finally returns 0.
Click cell C2 in your WPS Office spreadsheet where the repayment result should appear.
Type or paste this formula into C2: =IF(AND(B2="Plan 1",A2>22015),A2*0.09,IF(AND(B2="Plan 2",A2>27295),A2*0.09,IF(AND(B2="Plan 4",A2>27660),A2*0.09,IF(AND(B2="Plan 5",A2>25000),A2*0.09,IF(AND(B2="Postgraduate Loan",A2>21000),A2*0.06,0)))))
Press Enter to apply the formula, then change B2 to another plan or A2 to a different salary to confirm the result updates correctly.
Check the Drop-Down Values and Salary Cell If the Formula Returns 0
Use this if the formula is present but the result is 0 or appears incorrect despite selecting a plan.
Calculate Loan Repayment Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-style formulas such as nested IF, AND, and data validation dropdowns, so you can build the repayment calculator directly in WPS Office without switching to Microsoft Excel.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and create a new spreadsheet or open your existing .xlsx loan repayment workbook.
- 2. Set up the salary and plan cells: Enter the annual gross salary in A2 and create a repayment plan dropdown in B2 using Data > Validation.
- 3. Enter the repayment formula: In C2, paste the nested IF formula and press Enter.
- 4. Test the calculator: Change the dropdown selection in B2 and adjust A2 to confirm that C2 returns the correct repayment or zero.
Frequently Asked Questions
Why does the repayment formula return 0 even when I selected Plan 2?
Usually because B2 text does not exactly match the formula plan name or A2 is not above the Plan 2 threshold of 27295. Check for extra spaces and use Data > Validation to inspect the dropdown list values.
Can I use this formula in WPS Office on Windows and Android?
Yes. WPS Office Spreadsheets supports IF, AND, and nested formulas on Windows, macOS, Android, and iOS. The behavior is the same as entering the formula in the formula bar.
How do I create the repayment plan dropdown in B2?
Select B2, click Data > Validation, choose List, and enter Plan 1,Plan 2,Plan 4,Plan 5,Postgraduate Loan as the source, then apply.
What should I change if my salary is monthly instead of annual?
Convert the monthly salary to annual gross salary by multiplying by 12 or adjust the formula thresholds accordingly; the built-in formula assumes A2 is the annual gross amount.
Can I edit the repayment percentages in the formula?
Yes. Replace 0.09 or 0.06 in the formula with the required decimal rate, for example 0.10 for 10%, and save the workbook as .xlsx to retain Excel compatibility.
